CODEWITHSUNDEEP
sql-serversql-server-2008
Menno
Menno

Reputation: 12621

Select rows containing the group-wise maximum of a column

Say I've got a table named Item, which contains items with a price, set on a certain date.

Item

id | date       | price
---+------------+------
1  | 2014-01-01 | 1.09
1  | 2014-03-15 | 1.19
2  | 2014-04-02 | 2.75
1  | 2014-07-28 | 0.99
2  | 2014-08-07 | 2.35
3  | 2014-10-21 | 3.90

Now I'd like to select the current price per item (last record for each item):

WITH lastChange AS (
    SELECT id, MAX(date) date
    FROM Item
    GROUP BY id
)

SELECT Item.id, Item.price
FROM Item
JOIN lastChange ON Item.id = lastChange.id AND Item.date = lastChange.date

Giving:

id | price
---+------
1  | 0.99
2  | 2.35
3  | 3.90

I'm wondering whether there is a better way to make this selection.

Upvotes: 0

Views: 875

Answers (1)

Anon
Anon

Reputation: 10908

Windowing functions

WITH t AS (
  SELECT *
   ,MAX(date) OVER(PARTITION BY id) max_date_by_id
  FROM Item
)
SELECT *
FROM t
WHERE date = max_date_by_id

Cross apply

SELECT *
FROM Item t
CROSS APPLY (
  SELECT MAX(date) max_date_by_id
  FROM Item
  WHERE id = t.id
)
WHERE date = max_date_by_id

Upvotes: 3

Related Questions