How to Limit Rows in a SQL Server Result Set Database: MS SQL Server Operators: TOP OFFSET FETCH ROWS FIRST NEXT Table of Contents Problem: Example: Solution 1: Using OFFSET and FETCH Discussion: Solution 2: Using OFFSET and FETCH NEXT Solution 3: Using TOP with ORDER BY Solution 4: Using TOP with no ORDER BY 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: 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: 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: 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: nameprice The Robot Tobie185.50 Bike BMX200.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, 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: 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. Recommended courses: SQL Basics in SQL Server SQL Practice Set in MS SQL Server Common Functions in SQL Server Recommended articles: SQL Server Cheat Sheet Top 29 SQL Server Interview Questions How to Learn T-SQL Querying 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP 15 SQL Server Practice Exercises with Solutions See also: How to Number Rows in SQL How to Format a Date in T-SQL How to Split a String in SQL Server Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query