getting distinct records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting distinct records

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-16 : 13:02:19

how do i get an entire record returned when looking for only column to be unique?

basically i have 3 columns

id, tranid, appliedtranid

tranid can have multiple appliedtranids

i need to grab the top 10 distinct tranids but i need the entire record

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 13:30:03

I swear these DISTINCT queries are breeding! We've had several in the last couple of days.

So if a [tranid] has mutliple values of [appliedtranid] which one do you want?

MIN(appliedtranid) or MAX(appliedtranid) or Average(appliedtranid) ... or something else?

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 13:33:43

See also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91009#341596

and

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91066
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90903

tpiazza55
Posting Yak Master

162 Posts

Posted - 2007-10-16 : 13:38:23

actually it doesnt matter -- they all link back to the same company

so is it safe to say that if i go

select top 10 tranid, min(appliedtranid)
from table
group by tranid

it will return the top 10 distinct tranid

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 13:50:29

Yup, It would be a good idea to ahve an ORDER BY as well, otherwise you will get random results each time.

Maybe:

ORDER BY COUNT(*) DESC, tranid

to get the ones with the most appliedtranid first, and then use [tranid] as a tie break

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources