latest row for each of several values
-
I've got the following table structure, and I need to select the most recent record for each AssetID, filtered by the UserID:
ID AssetID UserID DateUpdated 2 100 1 2006-03-30 15:09:00 3 100 2 2006-03-30 15:10:00 4 101 2 2006-03-30 15:10:00 5 101 1 2006-03-30 15:11:00 6 102 1 2006-03-30 16:09:00 7 102 2 2006-03-30 16:10:00 8 103 2 2006-03-30 16:10:00 9 103 1 2006-03-30 16:11:00 So, if I wanted to see the Assets used by user 1, I could select:
select ID, AssetID, UserID, DateUpdated
from myTable
where UserID = 1
But this returns all the Assets even though Assets 102 and 100 are currently owned by User 2
There maybe more than 2 records for each asset, but I still need to list the latest entry,
for each asset, where the UserID is a specified value.
Any ideas?
-
Something like this ... ?
select myTable.ID, myTable.AssetID, myTable.UserID, myTable.DateUpdated
from myTable join (select t2.assetID, max(t2.dateupdated) from myTable t2 group by t2.assetid) t3 on myTable.AssetID = t3.AssetID and myTable.DateUpdated = t3.DateUpdated
where myTable.UserID = 1
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil, thanks for the suggestion, but it doesn't work. 🙁
If you compare these SQL statements (which come from your suggestion):
select t2.assetID, max(t2.dateupdated) as DateUpdated from tblAssetHistory t2 group by t2.assetid;
You'll see that this list the latest entry for each Asset (regardless of who currently has it). It's not possible to then filter them by selecting only those from the specified user.
If you modify it to read:
select t2.assetID, t2.UserID, max(t2.dateupdated) as DateUpdated from tblAssetHistory t2 group by t2.assetid, t2.UserID;
This one does list the user associated with each date entry, but it doesn't list one entry per Asset - it lists every entry per asset, so we still have the problem of selecting just the latest entry. If I could add the UserID column to the results, without including it in the 'group by' clause, this should work, but removing it from the 'group by 'clause just gives an error message.
The result is that your suggestion lists the latest entry for each asset, but also shows every asset which a user has owned, regardless of whether the user is the current user or not.
I think I'm close, my the final solution is being a little elusive...
Regards
Adam
-
You are correct in stating that the first piece of SQL identifies all the latest owners and their assets, but by performing a self join back to the orig table and then filtering on userID from the outer table, I still do not see why it doesn't work.
Can you post sample data and sample output that proves it doesn't work?
Aha - I have spotted an error in my SQL - missing column alias in the subquery. Try this:
select myTable.ID, myTable.AssetID, myTable.UserID, myTable.DateUpdated
from myTable join (select t2.assetID assetID, max(t2.dateupdated) dateupdated from myTable t2 group by t2.assetid) t3 on myTable.AssetID = t3.AssetID and myTable.DateUpdated = t3.DateUpdated
where myTable.UserID = 1
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil,
I'm not sure what I got wrong last time I tried it (I'd spotted the missing alias) but I've just tried it again and it works fine.
Thanks very much for your help,
Adam
-
Great - I like stories with happy endings
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply