How to Use ROW_NUMBER in SQLite
Window functions transformed SQLite's analytical capabilities when they were introduced in version 3.25.0 (September 2018). If you're running an older version, you'll need to upgrade to use...
Key Insights
- ROW_NUMBER() assigns sequential integers to rows and requires SQLite 3.25.0 or later—it’s the most straightforward window function for creating unique row identifiers within result sets
- Combining PARTITION BY with ORDER BY lets you restart numbering for each group, making it perfect for finding top N records per category or implementing grouped pagination
- Unlike RANK() and DENSE_RANK(), ROW_NUMBER() always produces unique sequential numbers even when ORDER BY columns contain duplicate values, which makes it ideal for deduplication and pagination scenarios
Introduction to Window Functions in SQLite
Window functions transformed SQLite’s analytical capabilities when they were introduced in version 3.25.0 (September 2018). If you’re running an older version, you’ll need to upgrade to use ROW_NUMBER() and other window functions.
ROW_NUMBER() assigns a unique sequential integer to each row in a result set. Unlike aggregate functions that collapse multiple rows into one, window functions perform calculations across sets of rows while preserving individual row identity. This makes ROW_NUMBER() invaluable for pagination, ranking, deduplication, and selecting top N records per group.
Here’s the simplest possible example:
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
price DECIMAL(10,2)
);
INSERT INTO products (name, price) VALUES
('Laptop', 999.99),
('Mouse', 29.99),
('Keyboard', 79.99),
('Monitor', 299.99);
SELECT
ROW_NUMBER() OVER () as row_num,
name,
price
FROM products;
This assigns numbers 1, 2, 3, 4 to the rows in whatever order SQLite retrieves them. Without an ORDER BY clause, the numbering is non-deterministic—you’ll get sequential numbers, but not in any guaranteed order.
Basic ROW_NUMBER Syntax and Usage
The real power emerges when you add ORDER BY to control the numbering sequence. The basic syntax is:
ROW_NUMBER() OVER (ORDER BY column [ASC|DESC])
Let’s look at practical examples with an employees table:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
hire_date DATE,
salary INTEGER
);
INSERT INTO employees (name, department, hire_date, salary) VALUES
('Alice', 'Engineering', '2020-01-15', 95000),
('Bob', 'Engineering', '2019-06-01', 105000),
('Carol', 'Sales', '2021-03-20', 75000),
('David', 'Sales', '2018-11-10', 85000),
('Eve', 'Engineering', '2022-02-14', 90000);
Number employees by hire date (earliest first):
SELECT
ROW_NUMBER() OVER (ORDER BY hire_date) as seniority_rank,
name,
hire_date
FROM employees;
Results:
seniority_rank | name | hire_date
1 | David | 2018-11-10
2 | Bob | 2019-06-01
3 | Alice | 2020-01-15
4 | Carol | 2021-03-20
5 | Eve | 2022-02-14
For products ordered by price (highest first):
SELECT
ROW_NUMBER() OVER (ORDER BY price DESC) as price_rank,
name,
price
FROM products;
The ORDER BY clause determines the sequence. Use DESC for descending order, ASC (default) for ascending.
Partitioning with PARTITION BY
PARTITION BY divides your result set into groups, restarting the row numbering for each partition. This is where ROW_NUMBER() becomes genuinely powerful.
Syntax:
ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)
Number employees within each department by hire date:
SELECT
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY hire_date
) as dept_seniority,
name,
department,
hire_date
FROM employees;
Results:
dept_seniority | name | department | hire_date
1 | Bob | Engineering | 2019-06-01
2 | Alice | Engineering | 2020-01-15
3 | Eve | Engineering | 2022-02-14
1 | David | Sales | 2018-11-10
2 | Carol | Sales | 2021-03-20
Notice how numbering restarts at 1 for each department. This pattern is essential for “top N per group” queries.
For an e-commerce scenario, rank products by price within categories:
CREATE TABLE products_full (
id INTEGER PRIMARY KEY,
name TEXT,
category TEXT,
price DECIMAL(10,2)
);
SELECT
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY price DESC
) as category_rank,
category,
name,
price
FROM products_full;
Practical Use Cases
Pagination
Implement offset-based pagination without OFFSET/LIMIT performance issues on large datasets:
WITH numbered_products AS (
SELECT
ROW_NUMBER() OVER (ORDER BY name) as row_num,
id,
name,
price
FROM products
)
SELECT id, name, price
FROM numbered_products
WHERE row_num BETWEEN 11 AND 20;
This retrieves rows 11-20 (page 2 with 10 items per page). While OFFSET can be slow on large offsets, this approach with appropriate indexes performs better.
Top N Per Group
Find the two highest-paid employees in each department:
WITH ranked_employees AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) as salary_rank,
name,
department,
salary
FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE salary_rank <= 2;
Finding and Removing Duplicates
Identify duplicate records (same name and department):
WITH duplicates AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY name, department
ORDER BY id
) as occurrence,
id,
name,
department
FROM employees
)
SELECT * FROM duplicates WHERE occurrence > 1;
Delete duplicates, keeping only the first occurrence:
DELETE FROM employees
WHERE id IN (
SELECT id FROM (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY name, department
ORDER BY id
) as occurrence
FROM employees
)
WHERE occurrence > 1
);
Getting First/Last Records Per Group
Find the most recently hired employee in each department:
WITH ranked AS (
SELECT
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY hire_date DESC
) as recency,
name,
department,
hire_date
FROM employees
)
SELECT name, department, hire_date
FROM ranked
WHERE recency = 1;
ROW_NUMBER vs RANK vs DENSE_RANK
SQLite provides three ranking functions with different behaviors for handling ties:
CREATE TABLE scores (
player TEXT,
score INTEGER
);
INSERT INTO scores VALUES
('Alice', 100),
('Bob', 95),
('Carol', 95),
('David', 90);
SELECT
player,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
RANK() OVER (ORDER BY score DESC) as rank,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM scores;
Results:
player | score | row_num | rank | dense_rank
Alice | 100 | 1 | 1 | 1
Bob | 95 | 2 | 2 | 2
Carol | 95 | 3 | 2 | 2
David | 90 | 4 | 4 | 3
ROW_NUMBER(): Always assigns unique sequential integers, even for ties. Use when you need guaranteed unique identifiers or don’t care about ties.
RANK(): Assigns the same rank to ties, then skips numbers. Bob and Carol both get rank 2, then David jumps to rank 4. Use for competitive rankings where ties share position.
DENSE_RANK(): Assigns the same rank to ties without skipping numbers. David gets rank 3 after the tie at rank 2. Use when you want consecutive rank numbers.
For pagination and deduplication, always use ROW_NUMBER(). For leaderboards and competitive rankings, choose RANK() or DENSE_RANK() based on whether you want to skip numbers after ties.
Performance Considerations and Best Practices
Window functions can be expensive on large datasets. Follow these guidelines:
Index Your ORDER BY Columns: Create indexes on columns used in ORDER BY clauses:
CREATE INDEX idx_employees_dept_hire
ON employees(department, hire_date);
-- This query now benefits from the index
SELECT
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY hire_date
) as rank,
name
FROM employees;
Use CTEs for Readability: Common Table Expressions make complex queries maintainable:
WITH ranked_data AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rn,
*
FROM products
)
SELECT * FROM ranked_data WHERE rn <= 5;
Avoid Multiple Window Functions on Large Tables: Each window function requires a separate pass. If possible, calculate multiple rankings in one query:
SELECT
ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
name,
department,
salary
FROM employees;
Consider Materialized Results: For frequently accessed rankings on large tables, store results in a separate table and refresh periodically rather than calculating on every query.
ROW_NUMBER() is an essential tool for modern SQLite development. Master its combination with PARTITION BY and ORDER BY, understand when to use it versus RANK() and DENSE_RANK(), and apply appropriate indexes for optimal performance. With these techniques, you can handle pagination, deduplication, and grouped rankings efficiently in your SQLite applications.