select the TOP N rows from a table

Question

I am making some paging, and I need to make some query and get the result form defined slicing . for example: I need to get all "top" rows in range 20n < x < 40n etc.

SELECT * FROM Reflow   WHERE ReflowProcessID = somenumber ORDER BY ID DESC; 

and now I need to make my sliding by column called ID .

Any suggestions how to so ? I need to run my query on mysql, mssql, and oracle.

Answer 1

Assuming your page size is 20 record, and you wanna get page number 2, here is how you would do it:

SQL Server, Oracle:

SELECT *   -- <-- pick any columns here from your table, if you wanna exclude the RowNumber FROM (SELECT ROW_NUMBER OVER(ORDER BY ID DESC) RowNumber, *        FROM Reflow         WHERE ReflowProcessID = somenumber) t WHERE RowNumber >= 20 AND RowNumber <= 40     

MySQL:

SELECT *  FROM Reflow   WHERE ReflowProcessID = somenumber ORDER BY ID DESC LIMIT 20 OFFSET 20 

Answer 2

In MySql, you can get 10 rows starting from row 20 using:

SELECT * FROM Reflow   WHERE ReflowProcessID = somenumber ORDER BY ID DESC LIMIT 10 OFFSET 20 --Equivalent to LIMIT 20, 10