Top n

LIMIT and OFFSET

syntax

SELECT columns FROM table LIMIT count OFFSET skip;

example

SELECT product_name, price
FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

output

-- Returns rows 21-30 (skips first 20, then takes 10)

Note MySQL/PostgreSQL use LIMIT. SQL Server uses TOP or FETCH FIRST. High OFFSET values degrade performance because the database still scans skipped rows.

RANK and DENSE_RANK

SQL · Window Functions

syntax

RANK() OVER (ORDER BY column)
DENSE_RANK() OVER (ORDER BY column)

example

SELECT
  product_name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

output

-- product_name | price  | rank | dense_rank
-- Laptop       | 999.00 | 1    | 1
-- Tablet       | 499.00 | 2    | 2
-- Phone        | 499.00 | 2    | 2
-- Keyboard     | 49.99  | 4    | 3  <-- rank skips 3, dense_rank doesn't

Note RANK leaves gaps after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). Choose based on whether you need contiguous numbers or positional accuracy.

SQLSQL