Mysql return specific row numbers

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 queri开发者_Python百科es (or 1 query and loop through the rows then only output the ones I need)

using php.

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?

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 QUERY:

If you are using 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
?>

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.

Here is my solution to select multiple rows by rownumber using one query:

SELECT * FROM
  (SELECT  @row := @row + 1 as row, t.*
   FROM `default_red_albums` t, (SELECT @row := 0) r) AS view
WHERE `row` IN(4,8)

The subquery returns a table with an extra "row" column. The mainquery just has to filter using a where statement.

tested on a 14000 row table and it took 0.0672 seconds.