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-

Solution 1:

Windowing functions

WITH t AS (
  SELECT*
   ,MAX(date) OVER(PARTITIONBY id) max_date_by_id
  FROM Item
)
SELECT*FROM t
WHEREdate= max_date_by_id

Cross apply

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