36

Possible Duplicate:
Select statement in SQLite recognizing row number

For example, SELECT * FROM table WHERE [row] BETWEEN x AND y

How can this be done? I've done some reading but haven't found anything specifically correct.

Imagine a list where you want results paged by an X amount of results, so for page 10 you would need results from rows 10 * X to 10 * X + X. Rather than display ALL results in one go

Community
  • 1
  • 1
rtheunissen
  • 7,347
  • 5
  • 34
  • 65
  • Don't you mean column? If you change your [row] with [column], you will receive a resultset with all rows that have a value in that particular column between X and Y. Also see: http://www.w3schools.com/sql/sql_between.asp – Jeroen Vannevel Dec 28 '12 at 11:30
  • 4
    i guess he wants something like "give me row 20 - 50" – SomeJavaGuy Dec 28 '12 at 11:32
  • Sorry if this was unclear. Imagine a list where you want results paged by an X amount of results, so for page 10 you would need results from rows 10 * X to 10 * X + X. Rather than display ALL results in one go. – rtheunissen Dec 28 '12 at 11:33
  • 1
    Look at http://stackoverflow.com/questions/8976925/select-statement-in-sqlite-recognizing-row-number – yatul Dec 28 '12 at 11:39
  • Or http://stackoverflow.com/questions/12233091/sqlite-equivalent-for-oracles-rownum – PhiLho Dec 28 '12 at 11:42

7 Answers7

78

For mysql you have limit, you can fire query as :

SELECT * FROM table limit 100` -- get 1st 100 records
SELECT * FROM table limit 100, 200` -- get 200 records beginning with row 101

For Oracle you can use rownum

See mysql select syntax and usage for limit here.

For SQLite, you have limit, offset. I haven't used SQLite but I checked it on SQLite Documentation. Check example for SQLite here.

Leandro Caniglia
  • 14,495
  • 4
  • 29
  • 51
Nandkumar Tekale
  • 16,024
  • 8
  • 58
  • 85
11

Following your clarification you're looking for limit:

SELECT * FROM `table` LIMIT 0, 10

This will display the first 10 results from the database.

SELECT * FROM `table` LIMIT 5, 5 .

Will display 5-9 (5,6,7,8,9)

The syntax follows the pattern:

SELECT * FROM `table` LIMIT [row to start at], [how many to include] .

The SQL for selecting rows where a column is between two values is:

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

See: http://www.w3schools.com/sql/sql_between.asp

If you want to go on the row number you can use rownum:

SELECT column_name(s)
FROM table_name
WHERE rownum 
BETWEEN x AND y

However we need to know which database engine you are using as rownum is different for most.

Felix D.
  • 4,811
  • 8
  • 38
  • 72
Pez Cuckow
  • 14,048
  • 16
  • 80
  • 130
9

You can use rownum :

SELECT * FROM table WHERE rownum > 10 and rownum <= 20
Majid Laissi
  • 19,188
  • 19
  • 68
  • 105
  • @paranoid-android wants something for SQLite. You pushed in the right direction, so I found the http://stackoverflow.com/questions/12233091/sqlite-equivalent-for-oracles-rownum answer which should work for him. – PhiLho Dec 28 '12 at 11:40
  • @PhiLho someone just added the SQLite tag :) – Majid Laissi Dec 28 '12 at 11:42
3

Assuming id is the primary key of table :

SELECT * FROM table WHERE id BETWEEN 10 AND 50

For first 20 results 

SELECT * FROM table order by id limit 20;
Raghvendra Parashar
  • 3,883
  • 1
  • 23
  • 36
2

Have you tried your own code?
This should work:

SELECT * FROM people WHERE age BETWEEN x AND y
Imanuel
  • 3,596
  • 4
  • 24
  • 46
2

Using Between condition

SELECT *
FROM TEST
WHERE COLUMN_NAME BETWEEN x AND y ;

Or using Just operators,

SELECT *
FROM TEST
WHERE COLUMN_NAME >= x AND COLUMN_NAME   <= y;
Jayamohan
  • 12,734
  • 2
  • 27
  • 41
2

Use the LIMIT clause:

/* rows x- y numbers */
SELECT * FROM tbl LIMIT x,y;

refer : http://dev.mysql.com/doc/refman/5.0/en/select.html

Rajshri
  • 4,163
  • 2
  • 15
  • 17