After I done the Zend Framework's database component, we attempted to abstract the functionality from the
LIMIT clause based on MySQL, PostgreSQL, and SQLite. That's, developing a query might be done by doing this:
$choose = $db->select() $choose->from('mytable') $choose->order('somecolumn') $choose->limit(10, 20)
Once the database supports
LIMIT, this produces an SQL query such as the following:
Choose * FROM mytable ORDER BY somecolumn LIMIT 10, 20
It was more complicated for brands of database that do not support
LIMIT (that clause isn't area of the standard SQL language, incidentally). If you're able to generate row amounts, result in the whole query a derived table, as well as in the outer query use
BETWEEN. It was the answer for Oracle and IBM DB2. Microsoft SQL Server 2005 includes a similar row-number function, so it's possible to write the query by doing this:
Choose z2.* FROM ( Choose 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 does not possess the
So my real question is, are you able to develop a method to emulate the
LIMIT functionality in Microsoft SQL Server 2000, exclusively using SQL? Without needing cursors or T-SQL or perhaps a saved procedure. It needs to support both arguments for
LIMIT, both count and offset. Solutions utilizing a temporary table will also be not acceptable.
The most typical solution for MS SQL Server 2000 appears to become such as the one below, for instance to obtain rows 50 through 75:
Choose TOP 25 * FROM ( Choose TOP 75 * FROM table ORDER BY BY area ASC ) a ORDER BY area DESC
However, this does not work when the total result set is, say 60 rows. The interior query returns 60 rows because that's within the top 75. Then your outer query returns rows 35-60, which does not easily fit in the preferred "page" of 50-75. Essentially, this solution works unless of course you'll need the final "page" of the result set that does not are actually a multiple from the page size.
Another solution works more effectively, but only when you are able to assume the end result set features a column that's unique:
Choose TOP n * FROM tablename WHERE key NOT IN ( Choose TOP x key FROM tablename ORDER BY key )
No general-purpose solution appears to exists for emulating
LIMIT in MS SQL Server 2000. A great choice is available if you're able to make use of the
ROW_NUMBER() function in MS SQL Server 2005.
I have seen that question before from clients, which was the very best explanation I have seen of your options and exactly what the impacts are:
If you want LIMIT only, ms sql has got the equivalent TOP keyword, to ensure that is obvious. If you want LIMIT with OFFSET, you can test some hacks like formerly referred to, however they all then add overhead, i.e. for ordering one of the ways and so the other, or even the expencive NOT Functioning. I believe all individuals cascades aren't needed. The cleanest solution during my oppinion could be only use TOP without offset around the SQL side, after which aim to the needed beginning record using the appropriate client method, like mssql_data_seek in php. Although this is not a pure SQL solution, It is the greatest one since it does not add any overhead (the missed-over records won't be moved around the network whenever you seek past them, if that's what worries you).
Choose TOP n * FROM tablename WHERE key NOT IN ( Choose TOP x key FROM tablename ORDER BY key DESC )