SELECT *
FROM (
SELECT TOP 50 *
FROM (
SELECT TOP 100 "TBL"."COL1",
"TBL"."COL2"
FROM "TBL"
ORDER BY "COL1" ASC,
"COL2" DESC
) AS inner_tbl
ORDER BY "COL1" ,
"COL2" ASC
) AS outer_tbl
ORDER BY "COL1" ,
"COL2" desc

Now here is the problem. As you can see in the innermost query the order by section is correct, ASC on COL1 and DESC on COL2 the same should be applied on all the outer tables but in reverse order. By doing this, then only we will get the correct result for the next page.

So, the correct query should be:

SELECT *
FROM (
SELECT TOP 50 *
FROM (
SELECT TOP 100 "TBL"."COL1",
"TBL"."COL2"
FROM "TBL"
ORDER BY "COL1" ASC,
"COL2" DESC
) AS inner_tbl
ORDER BY "COL1" DESC,
"COL2" ASC
) AS outer_tbl
ORDER BY "COL1" asc,
"COL2" desc

To fix this issue, I have reviewed the limit function of the Zend_Db_Adapter_Pdo_Mssql class which is: