SQL

ROW_NUMBER OVER PARTITION

by @admin ADMIN

1d ago

May 31, 2026

Public

0 0 up · 0 down Sign in to vote

Number rows within a partition — the most common "give me the Nth thing per group" pattern. Pair with a WHERE rn = 1 in an outer query to keep just the top row per group.

SQL

Raw

-- Most recent order per user
SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER     BY created_at DESC
         ) AS rn
  FROM   orders o
) t
WHERE rn = 1;

-- Top 3 highest-priced products per category
SELECT *
FROM (
  SELECT p.*,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER     BY price DESC
         ) AS rn
  FROM   products p
) t
WHERE rn <= 3;

-- PostgreSQL shortcut: DISTINCT ON (user_id) does the same for "first per group"
SELECT DISTINCT ON (user_id) *
FROM   orders
ORDER  BY user_id, created_at DESC;

Save your own code snippets

Create a free account and build your private vault. Share publicly whenever you want.

Get Started Free Sign In