CODEWITHSUNDEEP
sql-servert-sql
witts
witts

Reputation: 13

T-SQL, get highest values from two columns while grouping by one column

I'd like to select the highest price for each id, but only the prices from the rows with the highest lastupdated column.

Using this query won't give me the correct price.

SELECT 
    id, MAX(price) as price, MAX(lastupdated) as lastupdated 
FROM 
    tbl 
GROUP BY 
    id

Sample tbl data:

id, price, lastupdated
1, 50, 2015-03-01
1, 51, 2015-03-01
1, 52, 2015-03-01
1, 53, 2015-02-28
1, 54, 2015-02-28
1, 55, 2015-02-28
2, 20, 2015-02-01
2, 21, 2015-02-01
2, 22, 2015-02-01
2, 33, 2015-01-28
2, 34, 2015-01-28
2, 35, 2015-01-28

The result I'd like is:

id, price, lastupdated
1, 52, 2015-03-01
2, 22, 2015-02-01

Upvotes: 1

Views: 85

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use RANK to assign a ranking number to each record of your table. Then use this number in an outer query to select all records with the most recent lastupdated value per id: 

SELECT id, MAX(price) as price, lastupdated
FROM (
   SELECT id, price, lastupdated,
          RANK() OVER (PARTITION BY id ORDER BY lastupdated DESC) AS rnk
   FROM tbl ) t  
WHERE t.rnk = 1 
GROUP BY id, lastupdated

Alternatively, you can use HAVING clause to filter out any (id, lastupdated) groups not having the maximum lastupdated date value:

SELECT id, MAX(price) as price, lastupdated
FROM #tbl t1
GROUP BY id, lastupdated
HAVING lastupdated = (SELECT MAX(lastupdated) FROM #tbl t2 WHERE id = t1.id)

The query utilising the HAVING clause is more succint but less efficient, since the subquery it uses (SELECT MAX(lastupdated) ...) is evaluated once for each group defined in the outer query.

Upvotes: 3

fnurglewitz
fnurglewitz

Reputation: 2127

Today I feel like a cte is a good way to do it

with cte ( id, maxdate )
as
(
  select id, max(lastupdated)
  from tbl
  group by id
)
select 
     t3.id
    ,max(price)
    ,max(maxdate)
from 
    tbl t3 
        inner join cte on t3.id = cte.id and t3.lastupdated = cte.maxdate
group by
    t3.id

Upvotes: 1

jpw
jpw

Reputation: 44871

You need to find the lastupdated for each id and join with that:

SELECT tbl.id, MAX(price) as price, m.lastupdated
FROM tbl 
JOIN (
    SELECT id, MAX(lastupdated) AS lastupdated 
    FROM tbl 
    GROUP BY id
) m ON tbl.id = m.id and tbl.lastupdated = m.lastupdated
GROUP BY tbl.id, m.lastupdated
ORDER BY tbl.id

Upvotes: 1

Andomar
Andomar

Reputation: 238048

You could use a filtering join to only look at the rows with the latest date:

SELECT  tbl.id
,       MAX(price) as price
,       lastupdated 
FROM    Tel
JOIN    (
        SELECT  id
        ,       MAX(last_updated) as maxupdated
        FROM    tbl
        GROUP BY
                id
        ) mx
ON      tbl.id = mx.id
        AND tbl.lastupdated = mx.maxupdated
GROUP BY 
        tbl.id
,       last_updated

Upvotes: 1

Related Questions