ORDER BY, LIMIT, OFFSET

Sort the result, and give back only the first rows or one page of rows.

• Category: Querying
• Keywords: sort, order, sorting, ORDER BY, ASC, DESC, ascending, descending, NULLS FIRST, NULLS LAST, NULLS, FIRST, LAST, LIMIT, OFFSET, top N, first rows, paging, pagination, page, next page, LIMIT a, b, order by, limit, offset, asc, desc, paging
• See also: SELECT, WHERE (row-comparison), GROUP BY and HAVING, UNION and subqueries (union-all), Window and series functions, EXPLAIN

ORDER BY sorts the rows of the result. LIMIT keeps only the first rows, and OFFSET skips rows before them. They run last, after WHERE, GROUP BY and the select list, so they work on the final result.

Without ORDER BY, the order of the rows is not promised. Always add ORDER BY when the order matters (a "top 10", a page of posts, a report).

Example: The 3 days with the biggest volume, then the next 3 (sample data: sample)

SELECT time, symbol, volume FROM ochl_day ORDER BY volume DESC LIMIT 3;
SELECT time, symbol, volume FROM ochl_day ORDER BY volume DESC LIMIT 3 OFFSET 3;

Result:

SELECT time, symbol, volume FROM ochl_day ORDER BY volume DESC LIMIT 3

(3 rows)

SELECT time, symbol, volume FROM ochl_day ORDER BY volume DESC LIMIT 3 OFFSET 3

(3 rows)

ORDER BY

Syntax:

ORDER BY sort_key [ASC | DESC] [NULLS FIRST | NULLS LAST], ...

A sort_key can be:

With many keys, the second key sorts rows that are equal in the first key, and so on.

• Returns: the same rows, sorted.
• Notes: text is sorted by its bytes: 'A' comes before 'B', and every capital letter comes before every small letter ('Z' before 'a'). This is PostgreSQL's COLLATE "C". For dictionary order write ORDER BY name COLLATE "en_US" (COLLATE), or ORDER BY lower(name).
• An alias can be used alone (ORDER BY move), but not inside an expression (ORDER BY abs(move) is an error in a query without GROUP BY): write the expression again.
• After GROUP BY or with aggregates, ORDER BY can only use the output columns (names, aliases, positions) and expressions of them, like ORDER BY q / n.
• After UNION ALL, ORDER BY can only use output column names or positions (see UNION ALL).
• Rows that are equal in every key keep the order they came in. For stable pages, end with a unique column (like id).
• ORDER BY ... LIMIT n on a big table only keeps the best n rows while it reads, so it is fast. ORDER BY time DESC LIMIT n on a time-series table even stops early: it reads the newest rows first (see LIMIT).

Example: Top gainers of the day, sorted by an alias (sample data: sample)

SELECT symbol, open, close, round((close - open) / open * 100, 2) AS change_pct
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY change_pct DESC;

Result:

(3 rows)

Example: Sector first, then symbol inside each sector (sample data: sample)

SELECT sector, symbol, name FROM securities ORDER BY sector, symbol;

Result:

(5 rows)

Example: Sort by a column that is not shown, and by an expression (sample data: sample)

SELECT symbol, close FROM ochl_day WHERE time IN '2026-09-10' ORDER BY abs(close - open) DESC;

Result:

(3 rows)

Example: Newest news first (sample data: content-news)

SELECT published_at, symbol, title FROM news ORDER BY published_at DESC LIMIT 3;

Result:

(3 rows)

Example: Sort by position: the 3rd column (type), then the 2nd (name) (sample data: query-app)

SELECT code, name, type FROM accounts WHERE type IN ('income', 'expense') ORDER BY 3, 2;

Result:

(4 rows)

Example: Capital letters come before small letters; lower() fixes it (sample data: query-app)

SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY email;
SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY lower(email);

Result:

SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY email

(3 rows)

SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY lower(email)

(3 rows)

Example: An alias inside an expression is not known (sample data: sample; shows an error)

SELECT symbol, close - open AS move FROM ochl_day WHERE time IN '2026-09-10' ORDER BY abs(move);

Result:

Error: line 1, column 91: unknown column 'move' (table 'ochl_day' has: time symbol open high low close volume)

Example: A position must be one of the columns (sample data: sample; shows an error)

SELECT id, name FROM brokers ORDER BY 3;

Result:

Error: line 1, column 39: ORDER BY 3: there are only 2 columns

ASC, DESC

Syntax:

ORDER BY sort_key ASC
ORDER BY sort_key DESC

ASC (ascending) sorts small to big: 1, 2, 3; A, B, C; old times first. It is the default. DESC (descending) sorts big to small: 3, 2, 1; new times first.

• Notes: ASC or DESC belongs to one sort key. ORDER BY a DESC, b sorts a big first, and b small first.
• DESC also changes where NULL goes by default (see NULLS FIRST, NULLS LAST).

Example: Brokers by turnover, biggest first; equal turnover by id, smallest first (sample data: sample)

SELECT buyer, sum(amount) AS turnover
FROM trade_log
WHERE date IN '2026-09-10'
GROUP BY buyer
ORDER BY turnover DESC, buyer ASC;

Result:

(5 rows)

Example: The last 3 minutes of COAST on 2026-09-10 (sample data: sample)

SELECT time, close, volume FROM ochl_minutes
WHERE symbol = 'COAST' AND time IN '2026-09-10'
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

COLLATE: dictionary order

Syntax:

sort_key COLLATE "name"
a COLLATE "name" = b      (also <, >, BETWEEN, IN)

COLLATE picks the order for one ORDER BY key or one comparison. Without it, text compares byte by byte.

• Returns: a comparison gives true or false; ORDER BY sorts. SELECT name COLLATE "en_US" gives name unchanged.
• Notes: every language name uses the same dictionary order (a letter with an accent sorts right after the plain letter). An unknown name is an error.
• In ORDER BY, write the column or expression with COLLATE, not an alias or a position.
• COLLATE after a column type in CREATE TABLE is still read and ignored: write it in the query.
• Sorting with COLLATE works out a sort key for every row, so it is slower than a plain ORDER BY.

Example: Byte order, then dictionary order (sample data: query-app)

SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY email;
SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY email COLLATE "en_US";

Result:

SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY email

(3 rows)

SELECT email FROM app_users WHERE id IN (2, 4, 6) ORDER BY email COLLATE en_US

(3 rows)

Example: Case-insensitive match and natural order (sample data: sample)

SELECT 'Zebra' < 'apple' AS bytes, 'Zebra' COLLATE "en_US" < 'apple' AS dictionary,
       'apple' COLLATE nocase = 'APPLE' AS same, 'file2' COLLATE natural < 'file10' AS natural;

Result:

(1 row)

Example: An unknown collation (sample data: sample; shows an error)

SELECT 'a' COLLATE klingon = 'a';

Result:

Error: line 1, column 20: collation "klingon" does not exist; KDB knows "C", "en_US" (and other language names), "nocase" and "natural"

NULLS FIRST, NULLS LAST

Syntax:

ORDER BY sort_key [ASC | DESC] NULLS FIRST
ORDER BY sort_key [ASC | DESC] NULLS LAST

Say where rows with a NULL sort key go: before all other rows (NULLS FIRST) or after them (NULLS LAST).

• Notes: the defaults are the same as in PostgreSQL: NULL counts as bigger than every value.
• A common case: "last login, newest first" should show users who never logged in at the end: ORDER BY last_login DESC NULLS LAST.

Example: Recently active users first, never logged in last (sample data: query-app)

SELECT username, last_login FROM app_users ORDER BY last_login DESC NULLS LAST;

Result:

(8 rows)

Example: Without NULLS LAST, DESC puts NULL first (sample data: query-app)

SELECT username, last_login FROM app_users ORDER BY last_login DESC LIMIT 3;

Result:

(3 rows)

Example: Securities without a close today at the top (sample data: sample)

SELECT s.symbol, d.close
FROM securities s
LEFT JOIN ochl_day d ON d.symbol = s.symbol AND d.time IN '2026-09-10'
ORDER BY d.close NULLS FIRST;

Result:

(5 rows)

Example: NULLS needs FIRST or LAST (sample data: sample; shows an error)

SELECT id FROM brokers ORDER BY id NULLS;

Result:

Error: line 1, column 41: expected FIRST or LAST after NULLS but found ';'

LIMIT

Syntax:

LIMIT n
LIMIT n OFFSET m
LIMIT ALL
LIMIT lo, hi
LIMIT -n
LIMIT -n, -m
FETCH FIRST n ROWS ONLY

Gives back at most n rows. With ORDER BY, these are the first n rows in that order (a "top n").

• Returns: the first n rows, or fewer when there are fewer.
• Notes: LIMIT comes after ORDER BY, at the very end of the query. With UNION ALL, it works on all the rows together.
• n must be a plain number: not an expression, not negative.
• LIMIT ALL means no limit. The QuestDB form LIMIT lo, hi gives rows lo + 1 to hi, so LIMIT 10, 20 is the same as LIMIT 10 OFFSET 10. The standard FETCH FIRST n ROWS ONLY works too.
• LIMIT -n (QuestDB) gives the last n rows, still in their own order. On a time-series table these are the newest rows by time, and KDB reads only the newest partitions to find them. LIMIT -n, -m gives the last n rows without the final m: LIMIT -5, -2 is the 3 rows before the last 2. It cannot be used with OFFSET or FOR UPDATE.
• Server programs (kdb sql, HTTP /exec) also cut very big results; see limits.

Example: The 5 biggest trades of the trade log (sample data: sample)

SELECT transaction_id, symbol, quantity, rate, amount FROM trade_log ORDER BY amount DESC LIMIT 5;

Result:

(5 rows)

Example: Only the column names: LIMIT 0 (sample data: query-app)

SELECT * FROM app_users LIMIT 0;

Result:

(0 rows)

Example: The newest post of the forum (sample data: content-forum)

SELECT id, author, body, created_at FROM posts ORDER BY created_at DESC LIMIT 1;

Result:

(1 row)

Example: LIMIT -n: the newest 3 daily candles of CITY, oldest first (QuestDB) (sample data: sample)

SELECT time, symbol, close FROM ochl_day WHERE symbol = 'CITY' LIMIT -3;

Result:

(3 rows)

Example: LIMIT -n, -m: the last 5 rows without the final 2 (sample data: sample)

SELECT id FROM brokers ORDER BY id LIMIT -5, -2;

Result:

(3 rows)

Example: LIMIT lo, hi gives the rows between them (QuestDB) (sample data: sample)

SELECT id FROM brokers ORDER BY id LIMIT 2, 4;
SELECT id FROM brokers ORDER BY id LIMIT 2 OFFSET 2;
SELECT id FROM brokers ORDER BY id FETCH FIRST 2 ROWS ONLY;

Result:

SELECT id FROM brokers ORDER BY id LIMIT 2 OFFSET 2

(2 rows)

SELECT id FROM brokers ORDER BY id LIMIT 2 OFFSET 2

(2 rows)

SELECT id FROM brokers ORDER BY id LIMIT 2

(2 rows)

OFFSET

Syntax:

LIMIT n OFFSET m

Skips the first m rows, then gives back at most n rows.

• Returns: rows m + 1 to m + n. When m is past the end, no rows.
• Notes: OFFSET may be written after LIMIT, or by itself (then there is no limit), or before LIMIT as PostgreSQL allows. OFFSET m ROWS is accepted too.
• KDB still makes and sorts the skipped rows, so a big OFFSET is slow. See paging.

Example: Rows 4 to 6 by volume (sample data: sample)

SELECT time, symbol, volume FROM ochl_day ORDER BY volume DESC LIMIT 3 OFFSET 3;

Result:

(3 rows)

Example: Past the end: no rows (sample data: sample)

SELECT id, name FROM brokers ORDER BY id LIMIT 5 OFFSET 10;

Result:

(0 rows)

Example: OFFSET by itself: skip rows, keep the rest (sample data: sample)

SELECT id, name FROM brokers ORDER BY id OFFSET 2;

Result:

(3 rows)

Paging

Syntax:

-- page p (1, 2, 3, ...) with k rows per page:
ORDER BY key, unique_column LIMIT k OFFSET (p - 1) * k

-- keyset paging: the page after the last row shown
WHERE (key, unique_column) < (last_key, last_unique) ORDER BY key DESC, unique_column DESC LIMIT k

An app shows long lists one page at a time: 20 forum posts, then the next 20.

There are two ways:

• Notes: the page order must be unique: add a unique column like id as the last sort key. Otherwise rows with equal keys can move from one page to another.
• For the offset, KDB needs a whole number: compute (p - 1) * k in your program.

Example: Forum posts, 3 per page: page 1 (sample data: content-forum)

SELECT id, author, created_at FROM posts ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 0;

Result:

(3 rows)

Example: Page 2 (sample data: content-forum)

SELECT id, author, created_at FROM posts ORDER BY created_at DESC, id DESC LIMIT 3 OFFSET 3;

Result:

(3 rows)

Example: Page 2 with keyset paging: after post 6, the last row of page 1 (sample data: content-forum)

SELECT id, author, created_at FROM posts
WHERE (created_at, id) < ('2026-09-01T12:00:00+05:45', 6)
ORDER BY created_at DESC, id DESC
LIMIT 3;

Result:

(3 rows)

Example: A ledger page for the accountant: 5 lines per page, page 2 (sample data: query-app)

SELECT line_id, entry_date, account, debit, credit, memo
FROM ledger
ORDER BY entry_date, line_id
LIMIT 5 OFFSET 5;

Result:

(5 rows)