AdvancedBasic SELECT

FETCH FIRST: SQL standard alternative to LIMIT

The query

SQL

-- Standard SQL (works in PostgreSQL, Oracle, SQL Server 2012+)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

-- With OFFSET (PostgreSQL uses LIMIT/OFFSET but this is more portable)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;

-- WITH TIES: include tied rows
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS WITH TIES;

Tested against PostgreSQL 16

Note

“FETCH FIRST is SQL:2008 standard. WITH TIES includes rows that tie with the last row — can return more than N rows.

Tables referenced

employees10 cols