SQL

Retrieve Latest Record for Each Group Using ROW_NUMBER()

Efficiently fetch the most recent or latest entry for each distinct category or group in your SQL database, crucial for versioning and history tracking.

WITH RankedRecords AS (
  SELECT
    id,
    entity_id,
    created_at,
    value,
    ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY created_at DESC) as rn
  FROM
    entity_logs
)
SELECT
  id,
  entity_id,
  created_at,
  value
FROM
  RankedRecords
WHERE
  rn = 1;

How it works: This snippet uses a Common Table Expression (CTE) and the ROW_NUMBER() window function to find the latest record for each unique 'entity_id'. PARTITION BY entity_id divides the data into groups based on the entity, and ORDER BY created_at DESC assigns a sequential rank within each group, with rank 1 being the most recent entry. The final SELECT then retrieves only those records where 'rn' (rank number) is 1.

Retrieve Latest Record for Each Group Using ROW_NUMBER()

Need help integrating this into your project?