CODEWITHSUNDEEP
phpmysql
Hintswen
Hintswen

Reputation: 4007

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 queries (or 1 query and loop through the rows then only output the ones I need)

using php.

Upvotes: 2

Views: 6165

Answers (4)

Sarfraz
Sarfraz

Reputation: 382666

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
?>

Upvotes: 2

Chris Visser
Chris Visser

Reputation: 1647

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.

Upvotes: 0

Joel
Joel

Reputation: 3060

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.

Upvotes: 1

John Parker
John Parker

Reputation: 54425

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?

Upvotes: 4

Related Questions