CODEWITHSUNDEEP
mysqlsqlsql-serverqsqlquery
xarzu
xarzu

Reputation: 9479

How do I select from a specific number of rows?

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?

Upvotes: 4

Views: 17845

Answers (5)

Experienced_Learner
Experienced_Learner

Reputation: 41

The below information is specific to Informix RDBMS.

The SQL statement in below quoted portion will display all the first N=50 Records (i.e. from Row-0 till Row-49) of the selected table

SELECT FIRST 50 * from table_in_db

When we want to display a continuous sequence of ROWs which are placed in intermediate fashion in a table, we can use the SKIP directive to provide an offset from ROW-0 to indicate to Informix to start the selection from an intermediate ROW (10th ROW in below example) and then we can indicate the number of ROWs to be displayed from the 10th ROW until 50th ROW using the "FIRST" directive

SELECT SKIP 9 FIRST 41 * from table_in_db

Similarly when we want to display the ROWs from 15001 until 30000 - we can use the below SQL query.

SELECT SKIP 15000 FIRST 15000 * from table_in_db

To read more on this please refer the information available in the following IBM KnowledgeCenter web link : https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqlt.doc/ids_sqt_076.htm

Upvotes: 0

nawfal
nawfal

Reputation: 73183

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.

Upvotes: 10

Marsh
Marsh

Reputation: 173

For MSSQL the syntax is

SELECT * FROM table LIMIT 15000, 15000;

Upvotes: 0

Philippe Grondier
Philippe Grondier

Reputation: 11138

for mysql and some other databases:

SELECT * FROM table ORDER BY myField LIMIT numberOfRows OFFSET numberOfRowsToSkip

Upvotes: 0

user172839
user172839

Reputation: 1065

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

Upvotes: 3

Related Questions