ROW_NUMBER assigns a unique sequential number to each row. Essential for pagination, deduplication, and ranking.

Basic ROW_NUMBER

SELECT

ROW_NUMBER() OVER (ORDER BY id) as row_num,

name

FROM customers;

ROW_NUMBER for Ranking

SELECT

name,

sales,

ROW_NUMBER() OVER (ORDER BY sales DESC) as sales_rank

FROM salespeople;

ROW_NUMBER with PARTITION

SELECT

department,

name,

salary,

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank

FROM employees;

Remove Duplicates

WITH ranked AS (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn

FROM customers

)

SELECT * FROM ranked WHERE rn = 1;

Top N Per Group

WITH ranked AS (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn

FROM products

)

SELECT * FROM ranked WHERE rn <= 3;

Pagination with ROW_NUMBER

WITH numbered AS (

SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) as rn

FROM posts

)

SELECT * FROM numbered WHERE rn BETWEEN 11 AND 20;

Generate ROW_NUMBER Queries

AI2sql uses ROW_NUMBER for complex scenarios.