SQL

Retrieve Latest Record Per Group Using Window Functions

Efficiently fetch the most recent entry for each distinct category or user using SQL window functions, ideal for activity logs or user updates.

WITH RankedRecords AS (
  SELECT
    id, user_id, action_description, created_at,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) as rn
  FROM user_actions
)
SELECT id, user_id, action_description, created_at
FROM RankedRecords
WHERE rn = 1;

How it works: This query uses a Common Table Expression (CTE) and the `ROW_NUMBER()` window function. It partitions the data by `user_id` and orders it by `created_at` in descending order, assigning a rank to each record within its user group. The `WHERE rn = 1` clause then filters to retrieve only the latest action for each distinct `user_id`, which is a common requirement for activity feeds or tracking user's last interaction.

Retrieve Latest Record Per Group Using Window Functions

Need help integrating this into your project?