This query gives you the same ability as a LIMIT clause does in MySQL and Oracle for MS SQL Server without the headaches of stored procedures or temp tables.
Terms of Agreement:
By using this article, you agree to the following terms...
You may use
this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this article from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
The following query will emulate a
SELECT emp_id,lname,fname FROM employee LIMIT 20,10
That says, give me 10 records starting at record 21. So what will be returned are rows 21-30. This is used heavily in web-based apps so you can do recordset paging.
Here is what you can do in MS SQL to emulate it (this runs on the PUBS db):
select * from (
select top 10 emp_id,lname,fname from (
select top 30 emp_id,lname,fname
from employee
order by lname asc
) as newtbl order by lname desc
) as newtbl2 order by lname asc
The main thing to remember is to deal with all your ORDER BY clauses and also to use the inner TOP as the sum of what you are looking for. In this example - 30 since you start at 20 and want 10 rows (20+10=30).
Hope this helps you as it has helped me improve performance greatly on my web-based apps since I no longer have to transfer loads of data across the wire to get just the 10 results I display on a page.
Don't even think about using the internal ADO paging stuff if you have a lot of records to deal with, it will kill your performance as it must pull the entire recordset down to the local machine before it can do its work. Why bring down 20,000 records if you only want to display 10?
Enjoy!
Report Bad Submission
Your Vote
Other User Comments
6/30/2004 7:30:27 AM:
Good trick. But I just wonder a bit when you speak of performance and web paging. Wouldn't the ABSOLUTE directive of the fetch command be more cost effective as long as you can manage having a cursor? /Regards, Baard (If this comment was disrespectful, please report it.)
6/30/2004 7:57:02 AM:
The problem is using a client side recordsets is when you start messing with them to get the data and create the paging info, it will end up pulling down the entire recordset. What I have done to resolve the issue is use the above query with a COUNT() before it to get the total record count. It ends up much faster. I'm talking about querying over 200,000 records in most cases, when you are only working with 1000 records or so, then it's not really a big issue. (If this comment was disrespectful, please report it.)
6/30/2004 9:57:42 AM:
Couldn't you use rowcount to get rid of one of the selects?
set rowcount 10
select * from ( select top 30 emp_id,lname,fname from employee order by lname desc ) as newtbl order by lname asc
(If this comment was disrespectful, please report it.)
Nope, no RowCount. As soon as you touch that varible in a client side cursor ADO recordset used for paging, it will bring all the records down. So if you query has 100,000 records, all of them will get cached down and that is very very slow over the wire. See my above comment for more about that. (If this comment was disrespectful, please report it.)
Oops - misread your question. Actually if you try to use the SQL directive for ROWCOUNT, you'll notice it does not return the correct results. Try both queries and take a look. (If this comment was disrespectful, please report it.)
6/30/2004 9:10:32 PM:
Great trick! Thanks for sharing. It seems that if I have, let's say, 500,000 rows in a table, it would be a lot slower when I try to retrieve page that closed to the end of the table (e.q., showing the 49,001st page of 50,000 pages). Any suggestion please? Thanks! (If this comment was disrespectful, please report it.)
7/2/2004 4:10:30 AM:
i am working with more than 7 million records and this queries really works fine for the first 500,000. After that the same problem rise just as normal paging(which can not even work at all). what my suggest and what i always do is limit the number of records by specified more condition/filter rather than simply request all rows and send back to user. harly any user will like to page through more than 700,000 pages just to find one or few records. so ... this problem goes back to its original cause .... why do you want to list as much records as 200,000 in your application?
and the answer is simple, when dealing with quite a number of records, how user search and view the data is what we should be care of.
hope this do not offend anyone for .. i just try to share with you guys: how i deal with 7 million records. (If this comment was disrespectful, please report it.)
7/2/2004 4:13:05 AM:
oh .. one more thing ... this is quite a creative thought .. i appriciate your idea and thank you for giving me new ideas on my other project (If this comment was disrespectful, please report it.)
7/2/2004 1:35:53 PM:
I understand what you are saying. The issue is that you cannot always narrow down the records. Look at Google for example. In any case, even 5000 records can kill a web-application when the web server and the database server are not running on the same machine. This solution works great for that type of thing. With your amount of records, I'm sure you have all kind of issues dealing with that. The stored proc route and unique key val will be your best solution. Your issue may be more temp table problems than the size of the records on the wire. (If this comment was disrespectful, please report it.)
Just search this site for paging techniques and you'll find the stored proc routines. (If this comment was disrespectful, please report it.)
7/26/2004 5:41:23 PM:
novice question: how do i get this to work with more than one table. i have tried to substitute "newtabl" and "newtabl2" with table1 and table2 and the query fails? (If this comment was disrespectful, please report it.)
As you said we need the records from 21 to 30, but it will return the top 10 from the selected 30 records come from the query. so what is the catch of using this ????
(If this comment was disrespectful, please report it.)
I've always used sp_ExecuteSQL to do this within a stored procedure. You can build up your SQL, and it remembers the query plan for the next hit. (If this comment was disrespectful, please report it.)
11/24/2004 12:20:40 PM:
because answering is limited to 1000 chars, here's a link to why this approach doesn't work :)
http://terragen.de/limit.html
Rainer (If this comment was disrespectful, please report it.)
Yes that is correct. This does require a unique value. It's better than nothing which is what MS has given us so far for this! (If this comment was disrespectful, please report it.)
3/27/2005 7:25:06 PM:
very usefull article. I used to programming in PHP and MySQL, but i needed to change one application to SQL Server ... due that i made a function to work with both databases (you can choice) but: the query works well in SQL Server 2000 and dont works in SQL 7 (talking about ASC and DESC order) ... some idea to help me?
(If this comment was disrespectful, please report it.)
Should work fine on SQL7 as that is what I built it using. (If this comment was disrespectful, please report it.)
4/10/2005 8:00:39 AM:
This method does not work with informix. 1st: (If this comment was disrespectful, please report it.)
4/10/2005 8:02:43 AM:
this method does not work with informix, because subqueries are not allowed in FROM. Even if this would work, "FIRST" (the ifx equivalent of TOP) does not work in subqueries. any ideas? (If this comment was disrespectful, please report it.)
I use a similir query that might be useful for people who run in to sort order problems. I haven't looked at the problem above in depth, but I think you could get around grouped tables with a little effort. Here is what I use:
SELECT TOP 10 * FROM table01 WHERE (id NOT IN (SELECT TOP 20 id FROM table01 ORDER BY id desc)) ORDER BY id DESC; (If this comment was disrespectful, please report it.)
This is a great article... I've been working on a .NET project, but couldn't let go of my PHP mindset. Fortunately I don't need to work with .NET's standard paging stuff anymore (couldn't get it fixed).
I was wondering however how I can join 3 tables using this script. My database has over 2 million records so that paging stuff gives me server timed out messages and I'm working local!!! But I'm still struggeling to get joins to work... (If this comment was disrespectful, please report it.)
It is working fine. But, I have 3,00,000 rows in my query.I want to retreive 30,000 first time and another 30,000 next like that. Please help me (If this comment was disrespectful, please report it.)
You had a slight logic error. If you are starting at row 20 and want 10 records, you are retrieving rows 20-29 not 20-30. That would be 11 records. You need you subtract one from the total so that the query would be:
select * from ( select top 10 emp_id,lname,fname from ( select top 29 emp_id,lname,fname from employee order by lname asc ) as newtbl order by lname desc ) as newtbl2 order by lname asc (If this comment was disrespectful, please report it.)
Hello to all. Very helpful discussion so far. it really helped me and solved my problem upto a gr8 extent. i've been looking for same functionality as LIMIT in MySQL in MSSQL from long time. Thank you all. (If this comment was disrespectful, please report it.)
What if I have 21 records altogether? This code would return last 10 records everytime, not last one as someone might expect. (If this comment was disrespectful, please report it.)
What about: select top 10 * from ( select top 30 from employee emp_id,lname,fname order by lname) order by lname ? We'd do ir in two select statements instead of three. (If this comment was disrespectful, please report it.)
Nice solution, but it seems to miss the mark. Why not exploit your table design, relationships, and constraints to extract the required data instead of developing a work-around to LIMIT? Although physical record location within a table may seem reliable, no database I know of unequivocally guarantees the physical arrangement of record within a table will remain constant. (If this comment was disrespectful, please report it.)
This is pretty nice. I don't think it will work for me since my order by clause has 4 things in it and is configurable and would be hard to reverse reliably. I am trying to use the top to limit the number of rows returned, however, and it should improve performance in almost all queries.
The problem I have is to get the full row count of the query without the top in it. Is there any way other than running the query a second time with a count()? (If this comment was disrespectful, please report it.)
5/26/2007 3:54:44 PM:
please send me a code that counts number of votes using php and mysql. thank you in advance (If this comment was disrespectful, please report it.)
The method described in the top will not necessarily work if your order by clause does not use a unique ID. See Rainer's statement above :http://terragen.de/limit.html. Try this method instead: SELECT TOP [x] [fields] FROM [table] WHERE ([clause]) AND [pKey] NOT IN (
SELECT [pKey] FROM (
SELECT TOP [y] [fields2] FROM [table] WHERE [clause] ORDER BY [sort]) as [Tbl1])
ORDER BY [sort]
[x] = num rows [y] = 0 based offset [fields] = fields you want [table] = table / joins / whatever [clause] = where clause [pKey] = primary key of table / virtual view in [table] [fields2] = Only requires [pKey] and [sort] columns [sort] = normal sort. Identical for both incl. ASC / DESC [Tbl1] = name doesn't matter, it's for parser, not for you.
I have used this with 10's of thousands of rows, complex multiple joins and contions, with millisecond response. Memory usage is low, data sets are ONLY as big as needed, and NO client side filtering is required. (If this comment was disrespectful, please report it.)
I'm wondering on these techniques, that basically keep reselecting the same stuff and then outputting back 10 rows, how it compares to the following: 1. first get back the unique ids of all records and page on this. 2. then each time you want a page ask for the same query, except this time all the data columns that have in clause of the IDs for this page.
my guess would be that since you are talking about many many rows, this would be bad, even if key was relatively small (hmm is a guid small?). However, how would this method of selecting the data in SQL do versus the method I just described for a medium to small sized datasets, where total rows are at most say 2500?
any ideas? sql server would cache the basic data and then make smart use when basically paging through it on the server? or what?
thanks (If this comment was disrespectful, please report it.)
you da mannnn I just happen to need this RIGHT now. Thanks yo. (If this comment was disrespectful, please report it.)
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular article, please
click here instead.)
By 10 Users
6/30/2004 7:30:27 AM: 
