CODEWITHSUNDEEP
sqlgroup-bymax
user1823732
user1823732

Reputation: 41

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

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

Upvotes: 3

Views: 2928

Answers (2)

Rishabh Sagar
Rishabh Sagar

Reputation: 1814

If you are using sqllite (probably compatible with other DBs as well); you could do:

select 
    ct.id
    , ct.blownumber
    , time 
from 
    new 
    , (
        select 
            id
            , blownumber
            , max(ladle) as ldl 
        from 
            new 
        group by 
            id
            , blownumber
    ) ct
where 
    ct.id = new.id
    and ct.blownumber = new.blownumber
    and ct.ldl = new.ladle;

Upvotes: 0

marc_s
marc_s

Reputation: 755541

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.

Upvotes: 6

Related Questions