Limit function in MSSQL 2000
November 28th, 2009
Since there is no real limit functionality in MSSQL 2000 , getting a selection of records can be somewhat difficult. Well, here’s a solution to get a number of records that works like the “limit” function that can be found in MsSQL
SELECT col FROM (
SELECT top 50 * FROM (
SELECT top 200 * FROM tbl1
WHERE col > 1 ) ORDER BY col
) AS newtbl1 ORDER BY coldesc
) AS newtbl2 ORDER BY col ASC
SELECT top 50 * FROM (
SELECT top 200 * FROM tbl1
WHERE col > 1 ) ORDER BY col
) AS newtbl1 ORDER BY coldesc
) AS newtbl2 ORDER BY col ASC
Note that there is a similiar limit function in MSSQL 2k5 called “rownumber” which makes returning the desired results a lot easier. ;/