Mysql return specific row numbers

Question

I want to query a database and return specific row numbers eg 1, 10 and 25.

Is this possible or will I need to do 3 queries (or 1 query and loop through the rows then only output the ones I need)

using php.

Answer 1

you need to use the limit clause:

for first row:

 select * from table limit 0, 1

for tenth row:

 select * from table limit 9, 1

for 25th row:

 select * from table limit 24, 1  

SOLUTION WITH JUST ONE QUEERY:

If you are under php, you can use: mysql_fetch_row:

<?php
$result = mysql_query(" select * from table");
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$row = mysql_fetch_row($result);

echo $row[0]; // 1st
echo $row[9]; // 10th
    echo $row[24]; // 25th
?>

Answer 2

If there are specific IDs you require, you could simply use something along the lines of..

SELECT XXX FROM table WHERE XXX_id IN (1, 10, 25) ORDER BY XXX

...to get these rows back. If however you're just after arbitrary row numbers, then you'll have to use multiple queries as far as I'm aware. (Or use PHP, etc. to pluck the required rows back.)

Out of interest what are you trying to achieve?

Answer 3

If you need to do this often (and if the db is large), you have to also store the row numbers (more specifically - row id to number mappings) somewhere.

MyTable
-------
id
row_nr
...

and update the row_nr's every time you remove an entry.

i.e. - if you remove an entry with the id=10, you have to

UPDATE MyTable SET row_nr = row_nr - 1 WHERE id > 10

when adding rows use

INSERT INTO MyTable (whatever, row_nr)
VALUES ('thatever', (
SELECT MAX(MT2.row_nr)+1 FROM MyTable as MT2
))

--

Then you can use

SELECT XXX FROM MyTable WHERE row_nr IN (1, 10, 25)

when selecting things.