SQL
Retrieve the Latest Record for Each Group in SQL
Discover how to efficiently fetch only the most recent entry for distinct groups (e.g., latest update per product) using a Common Table Expression and window functions.
WITH RankedItems AS (
SELECT
item_id,
timestamp,
value,
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY timestamp DESC) as rn
FROM
item_history
)
SELECT
item_id,
timestamp,
value
FROM
RankedItems
WHERE
rn = 1;
How it works: This advanced query uses a Common Table Expression (CTE) and a window function to find the latest record for each distinct 'item_id'. 'ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY timestamp DESC)' assigns a rank to each item's history, with rank 1 being the most recent based on the 'timestamp'. The outer query then selects only those records with rank 1, effectively getting the latest entry for every item.