Returning a Specific Amount of Rows in T-SQL
Filed under SQL , SQL Server , T-SQL
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.
Jan5