7

I have a query ordered by NAME that return smt like this:

 ID     NAME
2121927 AAA
2123589 AAB
2121050 AAC
2463926 BBB ---> known ID
2120595 CCC
2122831 DDD
2493055 EEE
2123583 EEF

I need to know the next ID and the prev ID (if exists) of known ID && NAME How is it possible with only 1 query ?

Jonas
  • 121,568
  • 97
  • 310
  • 388
Luca
  • 73
  • 1
  • 3

2 Answers2

12
  SELECT *,
         'next'
    FROM table
   WHERE `name` > 'BBB'
ORDER BY `name`
   LIMIT 1

UNION

  SELECT *,
         'previous'
    FROM table
   WHERE `name` < 'BBB'
ORDER BY `name` DESC
   LIMIT 1

If you don't know particular BBB name field value - you could replace it with subquery like SELECT name FROM table WHERE id = 42, where 42 is the known ID value.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • lol, sorry :) i had correct result with next row, the prev row was be the first one because i ordered by ASC not DESC. – Luca Dec 16 '10 at 15:02
  • @Matěj Koubík: you need to add another column for consistency then, primary key for example – zerkms Jan 22 '14 at 19:27
  • Found it out myself: `SELECT * FROM table WHERE name >= 'a' AND (name > 'a' OR id > 123) ORDER BY name, id LIMIT 1` selects next record after id = 123, name = 'a' (name doesn't have to be unique). – Matěj Koubík Jan 24 '14 at 13:54
-5

I did it like this:
Its not the fastest, but it dont cares about order of fields.
Query all rows from db in order that you want.
Use php foreach to find current id.
Get array index of current id, make index +1 or index -1.

$current_id = 6;
$data = get_all_data_from_db();
foreach($data as $index=>$row) {
if($current_id == $row['id']) {
$next = $index+1;
$prev = $index-1;
}
$previous_row = $data[$prev];
$next_row = $data[$next];

Frantisek
  • 1
  • Seriously? A `foreach` ? When on the accepted answer there is a nice clean solution using a `UNION` of two simple queries. – Yaroslav Oct 11 '12 at 07:50