views:

6545

answers:

7

When I worked on the Zend Framework's database component, we tried to abstract the functionality of the LIMIT clause supported by MySQL, PostgreSQL, and SQLite. That is, creating a query could be done this way:

$select = $db->select();
$select->from('mytable');
$select->order('somecolumn');
$select->limit(10, 20);

When the database supports LIMIT, this produces an SQL query like the following:

SELECT * FROM mytable ORDER BY somecolumn LIMIT 10, 20

This was more complex for brands of database that don't support LIMIT (that clause is not part of the standard SQL language, by the way). If you can generate row numbers, make the whole query a derived table, and in the outer query use BETWEEN. This was the solution for Oracle and IBM DB2. Microsoft SQL Server 2005 has a similar row-number function, so one can write the query this way:

SELECT z2.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS zend_db_rownum, z1.*
    FROM ( ...original SQL query... ) z1
) z2
WHERE z2.zend_db_rownum BETWEEN @offset+1 AND @offset+@count;

However, Microsoft SQL Server 2000 doesn't have the ROW_NUMBER() function.

So my question is, can you come up with a way to emulate the LIMIT functionality in Microsoft SQL Server 2000, solely using SQL? Without using cursors or T-SQL or a stored procedure. It has to support both arguments for LIMIT, both count and offset. Solutions using a temporary table are also not acceptable.

Edit:

The most common solution for MS SQL Server 2000 seems to be like the one below, for example to get rows 50 through 75:

SELECT TOP 25 *
FROM ( 
  SELECT TOP 75 *
  FROM   table 
  ORDER BY BY field ASC
) a 
ORDER BY field DESC;

However, this doesn't work if the total result set is, say 60 rows. The inner query returns 60 rows because that's in the top 75. Then the outer query returns rows 35-60, which doesn't fit in the desired "page" of 50-75. Basically, this solution works unless you need the last "page" of a result set that doesn't happen to be a multiple of the page size.

Edit:

Another solution works better, but only if you can assume the result set includes a column that is unique:

SELECT TOP n *
FROM tablename
WHERE key NOT IN (
    SELECT TOP x key
    FROM tablename
    ORDER BY key
);

Conclusion:

No general-purpose solution seems to exist for emulating LIMIT in MS SQL Server 2000. A good solution exists if you can use the ROW_NUMBER() function in MS SQL Server 2005.

+10  A: 

I've seen that question before from clients, and this was the best explanation I've seen of what your options are and what the impacts are:

http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

Brent Ozar
That's great, there are several solutions described clearly in that article, and then tested for relative performance. Thanks!
Bill Karwin
+2  A: 

http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

hangy
Thanks, but the solutions in that thread use the SQL2005 ROW_NUMBER() function, which isn't available in SQL2000. Or else they use the TOP N subquery solution, which doesn't always work.
Bill Karwin
+1  A: 

SELECT TOP n * FROM tablename WHERE key NOT IN ( SELECT TOP x key FROM tablename ORDER BY key DESC );

Yes, this is close, but it only works when there's a unique key in the interim query result. How would you do it in a GROUP BY query or for a query that joins several tables?
Bill Karwin
+2  A: 

When you need LIMIT only, ms sql has the equivalent TOP keyword, so that is clear. When you need LIMIT with OFFSET, you can try some hacks like previously described, but they all add some overhead, i.e. for ordering one way and then the other, or the expencive NOT IN operation. I think all those cascades are not needed. The cleanest solution in my oppinion would be just use TOP without offset on the SQL side, and then seek to the required starting record with the appropriate client method, like mssql_data_seek in php. While this isn't a pure SQL solution, I think it is the best one because it doesn't add any overhead (the skipped-over records will not be transferred on the network when you seek past them, if that is what worries you).

I agree with your recommendation when designing an application. In my case, I was designing a database-access framework that was supposed to have a consistent API but produce different SQL as needed for different brands of database. The solution had to be in the SQL preparation, not in the fetching.
Bill Karwin
Nice idea, thank you very much. Overall I think that your solution is the more viable.
0plus1
A: 

hello if the number of rows returned by the query(**select top a from (select top b**)) is not a multiple of b,i mean a mod b<>0,the number remains a how can i solve this problem? and how to use ORDER BY ASC to sort the results thanks in advance

Yes, that's the point of the original question. You can solve it using MS SQL Server 2005 and the ROW_NUMBER() function, but the best we can do in MS SQL Server 2000 is probably the answer given by @grr, querying for the TOP @offset+@count rows, and then skip the first @offset rows using mssql_data_seek().
Bill Karwin
+1  A: 

Here is another solution which only works in Sql Server 2005 and newer because it uses the except statement. But I share it anyway. If you want to get the records 50 - 75 write:

select * from (
    SELECT top 75 COL1, COL2
    FROM MYTABLE order by COL3
) as foo
except
select * from (
    SELECT top 50 COL1, COL2
    FROM MYTABLE order by COL3
) as bar
Florian
Thanks, that looks like it would work too in Microsoft SQL Server 2005. It still leaves us with no good solution for Microsoft SQL Server 2000. But I guess since it's 2009, it's finally reasonable to drop support for 2000 anyway.
Bill Karwin
Yes, it works in Sql Server 2005.
Florian
A: 

http://msdotnetbuddy.blogspot.com/2010/07/emulating-limit-construct-from-mysql.html

sangram
I don't think EXCEPT (or at least it's not document) in MS SQL Server 2000.
Bill Karwin