select the TOP N rows from a table
154,415
Solution 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
Solution 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
Solution 3
select * from table_name LIMIT 100
remember this only works with MYSQL
Author by
Night Walker
Updated on July 07, 2020Comments
-
Night Walker almost 4 years
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.