Sorting & Limits

Control the order and number of rows returned by a query using ORDER BY, LIMIT, and OFFSET.

ORDER BY

Sorts the result set by one or more columns or expressions.

SELECT columns FROM table
ORDER BY expr1 [ASC|DESC], expr2 [ASC|DESC], ...;

• ASC (ascending) is the default sort direction.
• DESC sorts in descending order.

Single-Key Sort

-- Ascending (default)
SELECT name, salary FROM employees
ORDER BY salary;

-- Descending
SELECT name, salary FROM employees
ORDER BY salary DESC;

Multi-Key Sort

When two rows have the same value for the first key, the second key breaks the tie, and so on.

-- Sort by department ascending, then salary descending within each department
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Result:

NULLS FIRST / NULLS LAST

Controls where NULL values appear in the sorted output.

-- NULLs at the top
SELECT name, manager_id
FROM employees
ORDER BY manager_id ASC NULLS FIRST;

-- NULLs at the bottom (default for ASC)
SELECT name, manager_id
FROM employees
ORDER BY manager_id ASC NULLS LAST;

-- With DESC: NULLs at the bottom
SELECT name, bonus
FROM employees
ORDER BY bonus DESC NULLS LAST;

Sorting by Expressions

-- Sort by computed value
SELECT name, salary, salary * 12 AS annual
FROM employees
ORDER BY salary * 12 DESC;

-- Sort by alias
SELECT name, salary * 12 AS annual
FROM employees
ORDER BY annual DESC;

-- Sort by ordinal position (1-based column index)
SELECT name, department, salary
FROM employees
ORDER BY 2 ASC, 3 DESC;

LIMIT

Restricts the number of rows returned.

-- Top 5 earners
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Result:

OFFSET

Skips a number of rows before returning results. Typically combined with LIMIT for pagination.

-- Skip first 10 rows, return next 10 (page 2)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 10;

-- Skip first 20 rows, return next 10 (page 3)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 20;

Pagination Pattern

import { Context } from 'teide-js';

const ctx = new Context();
ctx.sqlSync(`CREATE TABLE products AS SELECT * FROM read_csv('products.csv')`);

const pageSize = 25;

async function getPage(page: number) {
  const offset = (page - 1) * pageSize;
  return ctx.sql(`
    SELECT id, name, price
    FROM products
    ORDER BY price DESC
    LIMIT ${pageSize} OFFSET ${offset}
  `);
}

// Get page 1
const page1 = await getPage(1);
// Get page 2
const page2 = await getPage(2);

LIMIT Without ORDER BY

Using LIMIT without ORDER BY returns an arbitrary subset of rows. The specific rows returned are not guaranteed to be consistent.

-- Get any 10 rows (useful for sampling / previewing)
SELECT * FROM large_table LIMIT 10;

Combined Example

-- Top 3 highest-paid employees per department
SELECT * FROM (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
WHERE rn <= 3
ORDER BY department ASC, salary DESC;