SQLite 3 reference · 11 entries
SQLite Window Functions
SQLite window functions: row_number(), rank(), dense_rank(), ntile(), lag(), lead(), first_value() and more, with OVER, PARTITION BY and frame examples.
A window function computes a value for each row from a set of related rows, defined by
OVER (PARTITION BY … ORDER BY …), without collapsing the rows the way GROUP BY does. Any aggregate
(sum(), avg(), …) can also be used with OVER.
| Name | Description | Since |
|---|---|---|
ROW_NUMBER() |
Number the rows of a window 1, 2, 3, … in the order given by the window's ORDER BY. Ties get different numbers. | 3.25.0 |
RANK() |
Rank rows by the window's ORDER BY, giving ties the same rank and leaving gaps after them (1, 2, 2, 4). | 3.25.0 |
DENSE_RANK() |
Rank rows by the window's ORDER BY, giving ties the same rank without gaps after them (1, 2, 2, 3). | 3.25.0 |
PERCENT_RANK() |
Return a row's relative rank from 0 to 1: (rank - 1) / (rows in partition - 1). | 3.25.0 |
CUME_DIST() |
Return the cumulative distribution: the share of rows ordered at or before the current row, from just above 0 to 1. | 3.25.0 |
NTILE() |
Split the rows of a window into N groups of nearly equal size and return each row's group number. | 3.25.0 |
LAG() |
Return a value from a previous row in the window, such as last month's total, or a default when there isn't one. | 3.25.0 |
LEAD() |
Return a value from a following row in the window, such as the next order's date, or a default when there isn't one. | 3.25.0 |
FIRST_VALUE() |
Return a value from the first row of the window frame, such as each department's highest salary. | 3.25.0 |
LAST_VALUE() |
Return a value from the last row of the window frame. Widen the frame to see the real last row of the partition. | 3.25.0 |
NTH_VALUE() |
Return a value from the Nth row of the window frame, or NULL if the frame has fewer rows. | 3.25.0 |