I originally tried this using a sub-query like:
SELECT Users.UserName, Items.ItemName FROM Users INNER JOIN Items WHERE Items.UserID = Users.UserID WHERE ItemID = (SELECT TOP 1 ItemID FROM Items WHERE UserID=Users.UserID ORDER BY Items.ItemDate DESC)
This worked but was prohibitively slow (the full query took 24 minutes). I found a much more efficient way to do it was like this:
SELECT Users.UserName, Items.ItemName FROM Users INNER JOIN (SELECT UserID, MAX(ItemDate) AS ItemDate FROM Items GROUP BY UserID) AS UserItemDate ON UserItemDate.UserID = Users.UserID INNER JOIN Items ON Item.UserID = Users.UserID AND Item.ItemDate = UserItemDate.ItemDate
This produced the same results much faster (21 seconds). In my case I actually made the UserItemDate query a view as I had to use it a couple of times. Note however that it can return multiple items for a user if they have the same date.
SELECT Users.UserName, Items.ItemName FROM Users INNER JOIN Items WHERE Items.UserID = Users.UserID WHERE ItemID = (SELECT TOP 1 ItemID FROM Items WHERE UserID=Users.UserID ORDER BY Items.ItemDate DESC)
This worked but was prohibitively slow (the full query took 24 minutes). I found a much more efficient way to do it was like this:
SELECT Users.UserName, Items.ItemName FROM Users INNER JOIN (SELECT UserID, MAX(ItemDate) AS ItemDate FROM Items GROUP BY UserID) AS UserItemDate ON UserItemDate.UserID = Users.UserID INNER JOIN Items ON Item.UserID = Users.UserID AND Item.ItemDate = UserItemDate.ItemDate
This produced the same results much faster (21 seconds). In my case I actually made the UserItemDate query a view as I had to use it a couple of times. Note however that it can return multiple items for a user if they have the same date.
RSS Feed