Need help with easy query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with easy query

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2005-09-26 : 14:00:39

The answer to this is probably quite easy, but I have not figured this out...

Say I have a table with three columns: UserID (int), BadgeID (int) and IssueDate (datetime). Each UserID can have multiple BadgeID, and the IssueDate for each BadgeID can be different.

What I am seeking is a simple query to return the most recently issued BadgeID for each ClientID. My first thought was

SELECT ClientID, BadgeID, max(IssueDate)
FROM Table
GROUP BY ClientID

But, this obviously doesn't work because BadgeID is not included in the GROUP BY clause. Furthermore, I really don't care to have Issuedate returned.

Looking forward to your suggestions. Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-26 : 14:27:12

Will this work for you?


select	ClientID
	,max(BadgeID) as BadgeID --in case 2 different badgeIDs have the same IssueDate
from	[Table] a
join	(
	select	ClientID
		,max(IssueDate) IssueDate
	from	[Table]
	Group by ClientID
	) b
		on a.ClientID = b.ClientID
group by ClientID

Be One with the Optimizer
TG

groston
Yak Posting Veteran

61 Posts

Posted - 2005-09-26 : 15:27:24

Thanks - was able to develop a solution based on your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources