Problem:
You’d like to limit the rows in a SQL Server result set.
Example:
Our database has a table named toy
with data in the columns id
, name
, and price
.
id | name | price |
---|---|---|
161 | Bike BMX | 200.00 |
121 | The Robot Tobie | 185.50 |
213 | Fishing game | 25.00 |
102 | Animal Puzzles | 45.80 |
111 | Race Track Set | 126.70 |
233 | Easy Bricks | 21.00 |
Let’s select toys’ names and prices. But we only need to see the first five rows.
Solution 1: Using OFFSET and FETCH
SELECT name, priceFROM toyORDER BY priceOFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
Here’s the result of the query:
name | price |
---|---|
Easy Bricks | 21.00 |
Fishing game | 25.00 |
Animal Puzzles | 45.80 |
Race Track Set | 126.70 |
The Robot Tobie | 185.50 |
Discussion:
To limit rows in the result set, use ORDER BY
with the optional OFFSET
and FETCH
clauses. First, the query sorts the rows (ORDER BY
). You then tell SQL Server which should be the first row in the result set (OFFSET...ROWS
) and how many rows to return (FETCH...ONLY
).
The OFFSET
clause is placed immediately after ORDER BY
. The integer indicates the first row to count (in SQL Server, the first row is 0, the second row is 1, etc.), followed by the keyword ROW
or ROWS
. You can use either ROW
or ROWS
, but it is recommended to use ROW
for 1 row and ROWS
for 0 and multiple rows. Look at the picture:
In our example, we sort by price (ORDER BY price
) and start returning results from the first row (OFFSET 0 ROWS
). Then we use FETCH
with the keyword FIRST
. You can use FIRST
or NEXT
; it is recommended to use FIRST
if you’re starting from the first row without omitting any records and NEXT
if any rows are omitted (i.e. if the OFFSET
is greater than 0).
Finally, we have the number of rows to select and the keyword ROWS ONLY
. Use ROWS ONLY
if you’re returning multiple rows and ROW ONLY
you’re limiting the result to one row. In our example, we limit it to five rows (FETCH FIRST 5 ROWS ONLY
).
Below, we have another query that limit rows to five but starts from the fifth row in the table (OFFSET 4 ROWS
):
Solution 2: Using OFFSET and FETCH NEXT
SELECT name, priceFROM toyORDER BY priceOFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY;
Here’s the result of the query:
name | price |
---|---|
The Robot Tobie | 185.50 |
Bike BMX | 200.00 |
Notice that this query returns only two rows, not the five indicated. Look at the picture:
Why only two rows? Because this table only contains six records. If we omit the first four rows and start on row 5, only two rows remain.
SQL Server has another way to limit rows: the TOP
clause.
Solution 3: Using TOP with ORDER BY
SELECT TOP 3 name, priceFROM toyORDER BY price;
Here’s the result of the query:
name | price |
---|---|
Easy Bricks | 21.00 |
Fishing game | 25.00 |
Animal Puzzles | 45.80 |
If you don’t need to omit any rows, you can use SQL Server’s TOP
clause to limit the rows returned. It is placed immediately after SELECT
. The TOP
keyword is followed by integer indicating the number of rows to return. In our example, we ordered by price and then limited the returned rows to 3.
Unlike the OFFSET – FETCH
method, TOP
doesn’t require ORDER BY
. Here’s a similar example, but without sorting the records:
Solution 4: Using TOP with no ORDER BY
SELECT TOP 3 name, priceFROM toy;
Here’s the result of the query:
Notice that different records have been returned. Usually (but not always) the rows will be shown in the order the records were inserted into the table.
We recommend using OFFSET
and FETCH
instead of TOP
, as OFFSET
and FETCH
are SQL standard; TOP
is specific to T-SQL.