Get Max of a field and also extract date max was created.
-
I have a table which holds revision number for an item an the date it was created.
How do I write a query that will return the highest Rev along with its date it was created?
Data
ITEM REV REV_DATE
---------- ------ -----------
0104004000 2 08/06/1993
0104004000 3 14/07/1995
-
SELECT ITEM, REV, DATE
FROM table1
INNER JOIN
(
SELECT ITEM, MAX(REV)
FROM table
GROUP BY ITEM
) AS table2
ON table1.ITEM = table2.ITEM
AND table1.REV = table2.REV
-- If only for a particular product
SELECT ITEM, REV, DATE
FROM table1
INNER JOIN
(
SELECT ITEM, MAX(REV)
FROM table
WHERE ITEM = @item
GROUP BY ITEM
) AS table2
ON table1.ITEM = table2.ITEM
AND table1.REV = table2.REV
WHERE ITEM = @item
---------------------------------------------
If data can screw you, it will; never assume the data are correct. -
Many thanks.
Given that the highest Rev will have a date greater than all the others. Would returning Max (Rev), Max (RevDate) and group by Item always give me the Max Rev and its corresponding Date?
-
If you can guarantee that to be the case then yes.
---------------------------------------------
If data can screw you, it will; never assume the data are correct. -
Another solution, this one not requiring a join
WITH CTE AS (SELECT ITEM, REV, DATE,
ROW_NUMBER() OVER(PARTITION BY ITEM ORDER BY REV DESC) AS rn
FROM mytable)
SELECT ITEM, REV, DATE
FROM CTE
WHERE rn=1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply