selecting all fields while using distinct ?

I am trying to select non-duplicate records from a table, however I still need to have ALL fields in the record returned. Here is the SQL I have right now:

select distinct LogID from adjustments order by LogID

This works fine as far as only getting records with non-duplicated LogIDs but does not return all the fields in each record! How can I select all the records that have distinct LogID's but have all the fields returned? I tried something like this

select distinct LogID, * from adjustments order by LogID

but this does not work, it still returns ALL records instead of only the distinct ones. what should I do?

what should you do? heh

DISTINCT applies to every column in the SELECT list

obviously, what you want is those rows that have the same LogID, but perhaps something else different

the only time you'd want SELECT DISTINCT * is when you wanted completely unique rows, in a situation where there can be completely duplicate rows, which usually never happens, especially if you're using an auto_increment anywhere

anyhow, try this:rudy
http://r937.com/

Nice ... but even shorter is:


select *
from adjustments
where LogID in ( select distinct LogID from adjustments )

sorry, Francis, but that will return them all

i'm glad you posted because i took another look at my solution, and it's wrong, it gets all the rows of duplicated logins, whereas Brian wanted all the rows of non-duplicated logins

Brian, change the greater than sign in my solution to equals

OOPS!

Thanks Rudy ... Can't imagine where I was!