post-page

MS SQL lack of the 2 argument LIMIT qualifier

1
response
by
 
on
October 26th, 2004
in
Cool Scripts, General

I ran into this recently where I really needed to use the LIMIT qualifier in some SQL queries and found out that there really is no equivalent in MS SQL, which is irritating.

In thinking about it and searching through the web, I think I have come up with a workaround that would work well if you were not dealing with millions of records.

Try this instead:
SELECT TOP 20 * from table_blah
WHERE index NOT IN (SELECT TOP 10 index from table_blah ORDER BY index)
ORDER BY index

instead of the LIMIT 10,20

heading
heading
One
Response

 

Comments

  1. Morné says:

    It generates this error:
    Warning: mssql_query(): message: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

    Obviously I did try it in all formats possible

    I have found this workaround:
    select * from (
    select top 20 index from (
    select top 10 index from table_blah order by index asc
    ) as newtbl order by index desc
    ) as newtbl2 order by index asc

    This works, but it’s still not as effective as LIMIT



Obviously Powered by WordPress. © 2003-2013

css.php