SQL query to get value of another column corresponding to a max value of a column based on group by

Question

I have the following table:

ID     BLOWNUMBER    TIME            LADLE
---    ----------    ----------      -----
124      1           01/01/2012        2
124      1           02/02/2012        1
124      1           03/02/2012        0
124      2           04/01/2012        1
125      2           04/06/2012        1
125      2           01/03/2012        0    

I want to have the TIME for the maximum value of LADLE for a group of ID & BLOWNUMBER.

Output required:

124        1       01/01/2012
124        2       04/01/2012
125        2       04/06/2012

Answer 1

If you're using SQL Server (or another engine which supports CTE's and ROW_NUMBER), you can use this CTE (Common Table Expression) query:

;WITH CTE AS 
(
   SELECT 
      ID, BlowNumber, [Time],
      RN = ROW_NUMBER() OVER (PARTITION BY ID, BLOWNUMBER ORDER BY [Time] DESC)
   FROM Sample
)
SELECT *
FROM CTE
WHERE RN = 1

See this SQL Fiddle here for an online live demo.

This CTE "partitions" your data by (ID, BLOWNUMBER), and the ROW_NUMBER() function hands out numbers, starting at 1, for each of those "partitions", ordered by the [Time] columns (newest time value first).

Then, you just select from that CTE and use RN = 1 to get the most recent of each data partition.