Returning a Specific Amount of Rows in T-SQL
T-SQL , SQL Server , SQL - Author:Raymond Lee at 3:50 PM Add comments
MySQL has a really helpful LIMIT feature that allows you to return a specific amount of rows from a query.
SQL Server 2005 doesn't have the LIMIT clause, but there are a few other functions that can be used instead.
First there's the SET ROW COUNT which allows you to specify the number of rows to return. The code below will return 7 Records:
SET ROWCOUNT 7
SELECT *
FROM tblMyTable
ORDER BY MyDate
Second there is the TOP function. This was added in SQL Server 7.0. TOP also limits the number of results, but it is used in the SELECT statement where you define which columns to grab. The following will return 15 rows with the highest 15 scores. (For lowest 15 scores, you would order ascending.)
SELECT TOP 15 Scores, StudenName
FROM tblMyTable
ORDER BY Scores DESC
TOP can also be used with the PERCENT keyword. Percentage can be any number from 1-100 and returns that percentage of records in the database. For example if I did the following in a table with 50 records, it would return 5 records.
SELECT TOP 10 PERCENT Scores, StudenName
FROM tblMyTable
ORDER BY Scores DESC
Although both behave the same, there are small differences in the two that can make unexpected results. If you are using it in a stored procedure, then SET ROWCOUNT is better as you can assign a variable to it. If you have multiple queries in a connection, then SET ROWCOUNT will override any queries after that. It can be fixed by using SET ROWCOUNT 0, or by using TOP instead. I haven't tried using both together, so I'm not sure if one will take precedence over another.
The one thing this lacks over the MySQL LIMIT command is MySQL lets you specify where to start taking records. For example LIMIT 100, 10 lets you take records 101 through 110. I found this post from _CodeJack_ where he uses 2 nested queries with TOP to get it. Basically he has one query looking for the TOP 110 records and orders them ascending. The next query grabbing the top 10 of the first query in descending order, and finally the last query will put it back into ascending order.
Jan 5, 2009 at 9:42 PM Here is another example of making MSSQL output LIMIT like functionality (along with pagination code): http://www.webveteran.com/blog/index.php/coldfusion/pagination-with-coldfusion-and-mssql-faux-mysqls-limit-xy/
Jan 7, 2009 at 8:15 AM With SQL 2005 you should also check out the ROW_NUMBER() function (in conjunction with the WITH statement) which you can use to generate row numbers for each row in the result set and then use a WHERE clause to filter the rows (e.g. return rows between 100 and 110). Check the SQL 2005 Books Online documentation for examples :)
Jan 14, 2009 at 2:22 PM http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx "Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function"
Jan 14, 2009 at 2:38 PM Thanks for the tips.
Jan 14, 2009 at 7:49 PM Thanks for those examples. They are a lot cleaner than the other one I was using.