SQL
Find Top N Records Per Group with ROW_NUMBER()
Efficiently retrieve the top N items (e.g., latest comment, highest score) for each distinct group within your dataset using the powerful `ROW_NUMBER()` window function.
WITH RankedPosts AS (
SELECT
post_id,
user_id,
post_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY post_date DESC) as rn
FROM user_posts
)
SELECT post_id, user_id, post_date
FROM RankedPosts
WHERE rn <= 3;
-- This query finds the 3 most recent posts for each user.
How it works: This query uses a Common Table Expression (CTE) and the `ROW_NUMBER()` window function to find the top 3 most recent posts for each `user_id`. `PARTITION BY user_id` divides the dataset into user-specific groups, and `ORDER BY post_date DESC` assigns a sequential rank within each group based on the post date, newest first. The outer query then selects only those posts with a rank of 3 or less, providing the 'top N per group' functionality.