Articles Cookbook
Back to list
SQL Server

How to Limit Rows in a SQL Server Result Set

Database:

Operators:

TOP, OFFSET, FETCH, ROWS, FIRST, NEXT

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.

idnameprice
161Bike BMX200.00
121The Robot Tobie185.50
213Fishing game25.00
102Animal Puzzles45.80
111Race Track Set126.70
233Easy Bricks21.00

Let’s select toys’ names and prices. But we only need to see the first five rows.

Solution 1:

SELECT name, price
FROM toy
ORDER BY price
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Here’s the result of the query:

nameprice
Easy Bricks21.00
Fishing game25.00
Animal Puzzles45.80
Race Track Set126.70
The Robot Tobie185.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:

How to Limit Rows in a SQL Server Result Set

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:

SELECT name, price
FROM toy
ORDER BY price
OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY;

Here’s the result of the query:

nameprice
The Robot Tobie185.50
Bike BMX200.00

Notice that this query returns only two rows, not the five indicated. Look at the picture:

How to Limit Rows in a SQL Server Result Set

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:

SELECT TOP 3
 name, price
FROM toy
ORDER BY price;

Here’s the result of the query:

nameprice
Easy Bricks21.00
Fishing game25.00
Animal Puzzles45.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:

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.

Recommended courses:

Recommended articles:

See also:

go to top