Grouping by most recent date
-
I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table: CostAverage PK CostAverageID FK UserItemID CostAverage OnDate At first glance, I threw down something like this: SELECT UserItemID, CostAverage, MAX(OnDate) FROM CostAverage GROUP BY UserItemID, CostAverage Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent. Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)
-
I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table: CostAverage PK CostAverageID FK UserItemID CostAverage OnDate At first glance, I threw down something like this: SELECT UserItemID, CostAverage, MAX(OnDate) FROM CostAverage GROUP BY UserItemID, CostAverage Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent. Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)
Try this
SELECT UserItemID, CostAverage,OnDate
FROM CostAverage ca1
where OnDate = (SELECT MAX(OnDate)
FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.
Bob Ashfield Consultants Ltd
-
Try this
SELECT UserItemID, CostAverage,OnDate
FROM CostAverage ca1
where OnDate = (SELECT MAX(OnDate)
FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.
Bob Ashfield Consultants Ltd
-
Thank you, that works quite well. The query runs around ~2 seconds, this table should be weeded out in the next 6 months during a refactor so I'm not too worried about performance yet. Again, thanks.