GROUPING Question - 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 2008 Forums

Transact-SQL (2008)

GROUPING Question

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2012-06-12 : 11:05:30

Hello,

I have a quick one, I hope. I have a query that returns a set of records as follows:

UserID MachineID LastUsed LastHourUsed
1 2 20120101 22
33 2 20120101 12
5 4 20120101 11
6 4 20120101 23

I need a select statement that will return one record for each of the two machines (id of 2 and id of 4) based off of the MAX(lasthourused) So I need to have it return:

1 2 20120101 22
6 4 20120101 23

No matter how I group it, I get all 4 records. I am trying to avoid running a script to loop through each record and pick the right one. I would prefer SQL. Any hints are appreciated.

Thanks!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-12 : 11:15:36

[code]
declare @dcarva table(UserID int, MachineID int, LastUsed datetime, LastHourUsed int)

insert into @dcarva
SELECT 1, 2, '20120101', 22
UNION
SELECT 33, 2, '20120101', 12
UNION
SELECT 5, 4, '20120101', 11
UNION
SELECT 6, 4, '20120101', 23

SELECT * FROM (
select * , ROW_NUMBER() OVER (PARTITION BY MachineID ORDER BY LastHourUsed DESC) AS Rn
from @dcarva
) Brazil
where Rn = 1
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

dcarva
Posting Yak Master

140 Posts

Posted - 2012-06-12 : 11:23:45

Thank you so much. That works like a charm. Interesting way to do it. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources