SQLite ROW_NUMBER(): PARTITION BY, Ordering and Top N Rows

SQLite ROW_NUMBER() assigns a sequential integer starting at 1 to each row in its window partition. Use it to number results, find the top N rows per group, or select one representative row from each group.

The ordering matters: ORDER BY inside OVER (...) determines the row-number assignment. Without it, SQLite can assign numbers in an unspecified order; when two rows tie on the window sort keys, their relative row numbers are not guaranteed.

Syntax

ROW_NUMBER() OVER (
    [PARTITION BY group_column, ...]
    ORDER BY sort_column [ASC | DESC], ...
)

• PARTITION BY is optional. It restarts numbering at 1 for each distinct group.
• ORDER BY inside the window defines the order used to assign numbers, not necessarily the output order of the whole query.
• Use a separate outer ORDER BY if the final display order matters.
• ROW_NUMBER() is a window function, available in SQLite 3.25.0 and later.

Number rows within each group

This example can run as-is without creating a table:

WITH scores(student, class, points) AS (
    VALUES
        ('Ana',  'A', 90),
        ('Ben',  'A', 90),
        ('Cara', 'A', 80),
        ('Dee',  'B', 95),
        ('Eli',  'B', 70)
)
SELECT
    class,
    student,
    points,
    ROW_NUMBER() OVER (
        PARTITION BY class
        ORDER BY points DESC, student ASC
    ) AS row_num
FROM scores
ORDER BY class, row_num;

class  student  points  row_num
-----  -------  ------  -------
A      Ana      90      1
A      Ben      90      2
A      Cara     80      3
B      Dee      95      1
B      Eli      70      2

For class A, Ana and Ben have the same points. Adding student ASC gives an explicit tie-breaker; for real tables, use a stable unique key if the sort columns can still tie.

Get the top two rows per group

SQLite does not allow a window-function result directly in the WHERE clause of the same query block. Compute the row number in a common table expression (CTE), then filter the numbered results:

WITH scores(student, class, points) AS (
    VALUES
        ('Ana', 'A', 90),
        ('Ben', 'A', 85),
        ('Cara', 'A', 70),
        ('Dee', 'B', 95),
        ('Eli', 'B', 70)
),
ranked AS (
    SELECT
        student, class, points,
        ROW_NUMBER() OVER (
            PARTITION BY class
            ORDER BY points DESC, student
        ) AS row_num
    FROM scores
)
SELECT class, student, points
FROM ranked
WHERE row_num <= 2
ORDER BY class, row_num;

class  student  points
-----  -------  ------
A      Ana      90
A      Ben      85
B      Dee      95
B      Eli      70

If the goal is to keep all tied rows at a rank boundary instead of exactly N rows, consider RANK() or DENSE_RANK().

ROW_NUMBER() versus RANK()

ROW_NUMBER() gives every row a different number, even when sort values tie. RANK() assigns tied values the same rank, with gaps after a tie:

WITH scores(points) AS (VALUES (90), (90), (80))
SELECT
    points,
    ROW_NUMBER() OVER (ORDER BY points DESC) AS row_num,
    RANK() OVER (ORDER BY points DESC) AS value_rank
FROM scores
ORDER BY points DESC;

points  row_num  value_rank
------  -------  ----------
90      1        1
90      2        1
80      3        3

The two 90-point rows are otherwise indistinguishable; do not rely on their assignment order when they represent different real records.

Common questions

Does PARTITION BY reduce the number of rows? No. Unlike GROUP BY aggregation, a window function preserves each input row and annotates it with a number.

Can I filter on row_num in the same SELECT’s WHERE? No. Put the window expression in a CTE or subquery, then filter its result in an outer query.

Does window ORDER BY sort the returned rows? It determines the numbering order, but use a query-level ORDER BY to guarantee display order.

For authoritative details, see SQLite window functions. You can run these examples in the SQLite Playground and modify the data or sort columns without installing a database.