SQL FIRST

What is the SQL FIRST keyword?

FIRST limits a query to return only the initial row(s) of a result set, most commonly used in the FETCH FIRST clause of Standard SQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FIRST: PostgreSQL 13+, Oracle 12c+, IBM Db2, Snowflake, SQL Server 2012+ (uses FETCH NEXT synonym), SQLite 3.25+; not supported in MySQL 8.0 (use LIMIT)

SQL FIRST Full Explanation

In Standard SQL, the keyword FIRST appears as part of the FETCH FIRST clause that limits the number of rows returned by a SELECT statement. It is functionally similar to LIMIT (MySQL, PostgreSQL), TOP (SQL Server), or ROWNUM (Oracle) but is fully ANSI-compliant. The clause follows an ORDER BY to guarantee predictable results. Omitting ORDER BY may yield nondeterministic output because the database can return rows in any order. FETCH FIRST can be combined with OFFSET for pagination and supports variations such as FETCH FIRST n ROWS ONLY, FETCH FIRST n ROW ONLY, and FETCH FIRST n PERCENT ROWS ONLY. Some systems also allow WITH TIES to include additional rows that share the same ordering value as the last fetched row.

SQL FIRST Syntax

SELECT column_list
FROM table_name
[WHERE condition]
ORDER BY sort_expression
FETCH FIRST n {ROW | ROWS} ONLY;

SQL FIRST Parameters

n (integer) - positive number of rows to return.
ROW (ROWS) - keyword|||optional plural form, both accepted.
PERCENT (optional in some systems) - keyword – returns the first n percent of rows.
WITH TIES (optional) - keyword – include rows that tie on the last ORDER BY value.

Example Queries Using SQL FIRST

-- Fetch the single most-recent hire
SELECT *
FROM employees
ORDER BY hire_date DESC
FETCH FIRST 1 ROW ONLY;

-- Fetch the first 10 orders for pagination
SELECT order_id, customer_id, total
FROM orders
ORDER BY order_date
FETCH FIRST 10 ROWS ONLY;

-- Fetch the top 5 percent highest-scoring students, including ties
SELECT *
FROM students
ORDER BY score DESC
FETCH FIRST 5 PERCENT ROWS WITH TIES;

Expected Output Using SQL FIRST

The query returns only the specified number of leading rows (or percentage) from the ordered result set
Remaining rows are not delivered to the client

Use Cases with SQL FIRST

Grab the single newest record (e.g., latest transaction)
Paginate large tables by retrieving small chunks
Produce top-n leaderboards
Improve performance by avoiding full-table transfers when only a subset is needed

Common Mistakes with SQL FIRST

Forgetting ORDER BY and assuming the database returns a meaningful first row
Using FIRST in a dialect that expects LIMIT or TOP instead
Confusing FETCH FIRST n ROWS ONLY with FETCH NEXT n ROWS ONLY (same effect but NEXT is more common in SQL Server)
Supplying a non-integer or negative n value

First Introduced In

SQL:2008 standard

Frequently Asked Questions

What is the difference between FETCH FIRST and FETCH NEXT?

Both clauses limit the number of rows. FETCH FIRST is usually used for the initial page, while FETCH NEXT reads like natural language when coupled with OFFSET for subsequent pages. Functionally they are identical.

Why should I always use ORDER BY with FETCH FIRST?

Without ORDER BY the database may return rows in an arbitrary order, making the concept of "first" meaningless. ORDER BY guarantees predictable and repeatable results.

Can I return a percentage of rows instead of a fixed number?

Yes. Many databases accept FETCH FIRST n PERCENT ROWS ONLY to return the top n percent of the ordered result set.

How do I include ties at the cutoff?

Add WITH TIES after the FETCH clause. The database will include any additional rows that share the same ORDER BY value as the last fetched row.