Back to list SQL Server How to Limit Rows in a SQL Server Result Set Database: SQL Server 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: 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: 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: SQL Basics SQL Basics in SQL Server SQL Practice Set Standard SQL Functions Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Number Rows in SQL How to Limit Rows in a MySQL Result Set How to Find the Number of Days Between Two Dates in MySQL How to Find the Last Day of the Month for a Given Date in MySQL How to Change Seconds to a Time Value in MySQL How to Change Datetime Formats in MySQL How to Get the Date from a Datetime Column in MySQL How to Get the Current Date and Time in MySQL How to Add Days to a Date in MySQL How to Add Time to a Datetime Value in MySQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.