CODEWITHSUNDEEP
mysql
Joe
Joe

Reputation: 55

Is it possible to return the rows between row 'x' and row 'y'?

I have a simple question regrading MySQL. Is it possible to return the rows between row 'x' and row 'y'? It's sort of hard to explain - for the sake of an example: Return rows 6 through 10, excluding rows 1-5 and rows 11+. Thanks! ;D

Upvotes: 2

Views: 92

Answers (6)

Jon Black
Jon Black

Reputation: 16559

mysql> select * from employees order by emp_id;
+--------+-----------------+---------+
| emp_id | name            | boss_id |
+--------+-----------------+---------+
|      1 | f00             |    NULL |
|      2 | ali later       |       1 |
|      3 | megan fox       |       1 |
|      4 | jessica alba    |       3 |
|      5 | eva longoria    |       3 |
|      6 | keira knightley |       5 |
|      7 | liv tyler       |       6 |
|      8 | sophie marceau  |       6 |
+--------+-----------------+---------+
8 rows in set (0.00 sec)

mysql> select * from employees order by emp_id limit 2,4;
+--------+-----------------+---------+
| emp_id | name            | boss_id |
+--------+-----------------+---------+
|      3 | megan fox       |       1 |
|      4 | jessica alba    |       3 |
|      5 | eva longoria    |       3 |
|      6 | keira knightley |       5 |
+--------+-----------------+---------+
4 rows in set (0.00 sec)

Upvotes: 0

ngduc
ngduc

Reputation: 1413

Why don't you use an Auto Increment field? Or you can use LIMIT keyword like:

SELECT * FROM tablename WHERE LIMIT 0, 5

This will show records 1,2,3,4,5

Upvotes: -1

bobannn
bobannn

Reputation: 1

From the manual (http://dev.mysql.com/doc/refman/5.0/en/select.html):

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

Upvotes: 0

Matti Virkkunen
Matti Virkkunen

Reputation: 65126

Use LIMIT. Remember to combine it with ORDER BY for the results to make any sense.

SELECT fields, ...
FROM table
ORDER BY something_sensible
LIMIT 5, 5

(Start from row 6, take 5 rows)

Upvotes: 4

Abe Miessler
Abe Miessler

Reputation: 85046

Yes, here's an example:

SELECT * FROM myTable LIMIT 5, 5

Upvotes: 2

bensiu
bensiu

Reputation: 25564

SELECT * FROM table LIMIT 5, 5

http://dev.mysql.com/doc/refman/5.5/en/select.html and look at LIMIT section

Upvotes: 2

Related Questions