How do I select from a specific number of rows?

Question

In a SQL query, how do I select a specific number of rows from row number 10 to 50, for example.

SELECT top 15000 [ID].... 

will get the first 15000 rows, but what would I do if I wanted to get the next 15000?

Answer 1

The syntax for MySQL would be

SELECT * FROM table LIMIT numberOfRowsToSkip, numberOfRowsToSelect

So in your case:

SELECT * FROM table LIMIT 9, 41; --selects from row no. 10 to no. 50

SELECT * FROM table LIMIT 15000, 15000; --selects from 15001st row, next 15000 rows

For reference visit MySQL SELECT documentation. Philippe provides an alternate syntax to this.

For SQL Server, see this.

Answer 2

SELECT *
FROM
(
SELECT 
ROW_NUMBER() OVER (ORDER BY ID),
*
FROM table
)  
WHERE ROW_NUMBER() between [start_range] and [end_range]