How to Limit Rows in a SQL Server Result Set

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.

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, price
FROM toy
ORDER BY price
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Here’s the result of the query:

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, price
FROM toy
ORDER BY price
OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY;

Here’s the result of the query:

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, price
FROM toy
ORDER BY price;

Here’s the result of the query:

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, price
FROM 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.