DISTINCT one column but show all columns

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

SQL Server 2005 Forums

Transact-SQL (2005)

DISTINCT one column but show all columns

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2011-03-07 : 14:32:55

I have a table hwsn, userid, date. hwsn has duplicate entries so I only want to have the most recent of the dupilcates show and all unique hwsn. Any ideas?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-07 : 14:49:47

SELECT h.userid,h.date
FROM
(select userid,date, [rn] = rank() over(partition by userid order by date desc)
from hwns
) h

WHERE rn = 1

Everyday I learn something that somebody else already knew

darms21
Yak Posting Veteran

54 Posts

Posted - 2011-03-07 : 15:01:36

I meant to say I have a table Assets with columns hwsn, userid and date. I want to show all unique hwsn records along w/ the most recent hwsn according to date if it is a duplicate.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-07 : 15:06:42

SELECT h.hwsn,h.userid,h.date
FROM
(select hwsn,userid,date, [rn] = rank() over(partition by hwsn order by date desc)
from assets
) h

WHERE rn = 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources