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
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