November 6, 2005 at 12:21 pm
Simplified schema:
RowID int (auto-increment), UserName varchar(50), EventDate (datetime)
Sample data
1,'Test',11062005 12:00
2,'Test',11062005 12:01
3,'Test',11062005 11:59
4,'Test',11052005 12:00
The MAX RowID will not necessarily be the MAX EventDate for a specific user - the data is retrieved from devices in a polling sequence so device 1 may have data from later times than device 2, but since it is polled first, it's data will have a lower RowID. Also, precision in the devices is only to the minute so, there could be entries with the same date time, in which case I need the record with the MAX(EventDate) and MAX(RowID).
I need a query to return
SELECT MAX(EventDate), UserName, RowID GROUP BY UserName --doesn't work of course without aggregate for RowID
How do I aggregate this so that if EventDate is the same for multiple records, it returns the one record with the MAX(EventDate) and MAX(RowID)? If I modify the above to:
SELECT MAX(EventDate), UserName, MAX(RowID) GROUP BY UserName
It returns the MAX value for each column, but not necessarily in the same record.
For the sample data above, I would need:
11062005 12:01, 'Test', 2
..and if there were multiple records with that time, I would need the MAX RowID with that latest datetime. Hopefully, that makes sense...
November 6, 2005 at 2:20 pm
SELECT EventDate, UserName, RowID
FROM ....
WHERE EventDate IN (SELECT MAX(EventDate) FROM ....)
November 7, 2005 at 3:10 am
You can solve this kind of problems with multiple agregates by nesting derived tables. The idea is that you first find one of the MAX values for the group, then join outwards on the groupmember to the next 'level' to find the next MAX value, using the groupmember and the previously found MAX value(s)
Example:
create table #x (id int not null, username char(5) not null, eventdate datetime not null )
go
insert #x select 1, 'Test', '20051106 12:00'
insert #x select 2, 'Test', '20051106 12:01'
insert #x select 3, 'Test', '20051106 11:59'
insert #x select 4, 'Test', '20051106 12:00'
go
select x.username, x.maxDate, max(y.id) as maxId
from (
select username, max(eventdate) as maxDate
from #x
group by username
) x
join #x y
on x.username = y.username
and x.maxDate = y.eventdate
group by x.username, x.maxDate
go
drop table #x
go
username maxDate maxId
-------- ------------------------------------------------------ -----------
Test 2005-11-06 12:01:00.000 2
/Kenneth
November 7, 2005 at 6:27 am
Thanks a lot Kenneth - that did the trick. I appreciate the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply