[sql] How can I select rows by range?

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

This question is related to sql sqlite

The answer is


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.


You can use rownum :

SELECT * FROM table WHERE rownum > 10 and rownum <= 20

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;

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;

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


Have you tried your own code?
This should work:

SELECT * FROM people WHERE age BETWEEN x AND y