3

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?

nawfal
  • 62,042
  • 48
  • 302
  • 339
xarzu
  • 7,579
  • 35
  • 100
  • 140

5 Answers5

6

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.

Community
  • 1
  • 1
nawfal
  • 62,042
  • 48
  • 302
  • 339
  • So why would there be a downvote? The question is tagged with MySQL. Its not a bad database :) – nawfal Sep 17 '13 at 06:46
  • @user172839 right, but when a question is tagged with something, it means he needs to know the answer for that tag too. Its not right to downvote answers that only answer partially. In fact very few would know the answer for all MySQL, TSQL, and QSQL.. – nawfal Sep 17 '13 at 06:53
  • There are some people who just put downvote without thinking as like our friend @user172839. Its waste of time arguing with them, we should let them go in their own path :) – DB_learner Sep 17 '13 at 07:22
  • @Mani lets not be harsh, he canceled his vote, which is good. – nawfal Sep 17 '13 at 08:19
2
SELECT *
FROM
(
SELECT 
ROW_NUMBER() OVER (ORDER BY ID),
*
FROM table
)  
WHERE ROW_NUMBER() between [start_range] and [end_range]
user172839
  • 983
  • 1
  • 9
  • 19
0

for mysql and some other databases:

SELECT * FROM table ORDER BY myField LIMIT numberOfRows OFFSET numberOfRowsToSkip
Philippe Grondier
  • 10,395
  • 3
  • 29
  • 67
0

For MSSQL the syntax is

SELECT * FROM table LIMIT 15000, 15000;
nawfal
  • 62,042
  • 48
  • 302
  • 339
Marsh
  • 83
  • 1
  • 11
  • I hope you meant MySQL. For MS SQLServer, that is not correct. See this: http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server – nawfal Sep 17 '13 at 14:27
0

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

Experienced_Learner
  • 31
  • 5