29

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,079
  • 5
  • 31
  • 63
  • 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

70

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
  • 13,815
  • 4
  • 29
  • 46
Nandkumar Tekale
  • 15,514
  • 7
  • 56
  • 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,417
  • 6
  • 37
  • 68
Pez Cuckow
  • 13,592
  • 15
  • 77
  • 127
9

You can use rownum :

SELECT * FROM table WHERE rownum > 10 and rownum <= 20
Majid Laissi
  • 18,348
  • 18
  • 64
  • 104
  • @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
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,469
  • 2
  • 26
  • 40
1

Have you tried your own code?
This should work:

SELECT * FROM people WHERE age BETWEEN x AND y
Pharaoh
  • 3,487
  • 4
  • 23
  • 44
1

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,013
  • 2
  • 14
  • 17
1

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,657
  • 22
  • 36