Databases
StackOverflow2013
Postsdbo
POEmulate MySQL LIMIT clause in Microsoft SQL Server 2000
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEmulate MySQL LIMIT clause in Microsoft SQL Server 2000
    primarykey
    data
    text
    <p>When I worked on the <a href="http://framework.zend.com/manual/en/zend.db.html" rel="noreferrer">Zend Framework's database component</a>, we tried to abstract the functionality of the <code>LIMIT</code> clause supported by MySQL, PostgreSQL, and SQLite. That is, creating a query could be done this way:</p> <pre><code>$select = $db-&gt;select(); $select-&gt;from('mytable'); $select-&gt;order('somecolumn'); $select-&gt;limit(10, 20); </code></pre> <p>When the database supports <code>LIMIT</code>, this produces an SQL query like the following:</p> <pre><code>SELECT * FROM mytable ORDER BY somecolumn LIMIT 10, 20 </code></pre> <p>This was more complex for brands of database that don't support <code>LIMIT</code> (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 <code>BETWEEN</code>. 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:</p> <pre><code>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; </code></pre> <p>However, Microsoft SQL Server 2000 doesn't have the <code>ROW_NUMBER()</code> function.</p> <p>So my question is, can you come up with a way to emulate the <code>LIMIT</code> 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 <code>LIMIT</code>, both count and offset. Solutions using a temporary table are also not acceptable.</p> <p><strong>Edit:</strong></p> <p>The most common solution for MS SQL Server 2000 seems to be like the one below, for example to get rows 50 through 75:</p> <pre><code>SELECT TOP 25 * FROM ( SELECT TOP 75 * FROM table ORDER BY BY field ASC ) a ORDER BY field DESC; </code></pre> <p>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.</p> <p><strong>Edit:</strong></p> <p>Another solution works better, but only if you can assume the result set includes a column that is unique:</p> <pre><code>SELECT TOP n * FROM tablename WHERE key NOT IN ( SELECT TOP x key FROM tablename ORDER BY key ); </code></pre> <p><strong>Conclusion:</strong></p> <p>No general-purpose solution seems to exist for emulating <code>LIMIT</code> in MS SQL Server 2000. A good solution exists if you can use the <code>ROW_NUMBER()</code> function in MS SQL Server 2005.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBill Karwin
    1. USBill Karwin
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POEmulate MySQL LIMIT clause in Microsoft SQL Server 2000
      1. USMark Brackett
      1. VTFavorite
    2. VO
      singulars
      1. POEmulate MySQL LIMIT clause in Microsoft SQL Server 2000
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POEmulate MySQL LIMIT clause in Microsoft SQL Server 2000
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload