How to get next/previous record in MySQL?

问题:

Say I have records with IDs 3,4,7,9 and I want to be able to go from one to another by navigation via next/previous links. The problem is, that I don\'t know how to fetch record with nearest higher ID.

So when I have a record with ID 4, I need to be able to fetch next existing record, which would be 7. The query would probably look something like

SELECT * FROM foo WHERE id = 4 OFFSET 1

How can I fetch next/previous record without fetching the whole result set and manually iterating?

I\'m using MySQL 5.

回答1:

next:

select * from foo where id = (select min(id) from foo where id > 4)

previous:

select * from foo where id = (select max(id) from foo where id < 4)

回答2:

In addition to cemkalyoncu\'s solution:

next record:

SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;

previous record:

SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;

edit: Since this answer has been getting a few upvotes lately, I really want to stress the comment I made earlier about understanding that a primary key colum is not meant as a column to sort by, because MySQL does not guarantee that higher, auto incremented, values are necessarily added at a later time.

If you don\'t care about this, and simply need the record with a higher (or lower) id then this will suffice. Just don\'t use this as a means to determine whether a record is actually added later (or earlier). In stead, consider using a datetime column to sort by, for instance.

回答3:

All the above solutions require two database calls. The below sql code combine two sql statements into one.

select * from foo 
where ( 
        id = IFNULL((select min(id) from foo where id > 4),0) 
        or  id = IFNULL((select max(id) from foo where id < 4),0)
      )    

回答4:

SELECT * FROM foo WHERE id>4 ORDER BY id LIMIT 1

回答5:

I was attempting to do something similar to this, but I needed the results ordered by date since I can\'t rely on the ID field as a sortable column. Here\'s the solution I came up with.

First we find out the index of the desired record in the table, when it\'s sorted as we want:

SELECT row
FROM 
(SELECT @rownum:=@rownum+1 row, a.* 
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id) as article_with_rows
WHERE id = 50;

Then decrement the result by 2 put it in the limit statement. For example the above returned 21 for me so I run:

SELECT * 
FROM articles
ORDER BY date, id
LIMIT 19, 3

Gives you your primary record along with it\'s next and previous records given your stated order.

I tried to do it as a single database call, but couldn\'t get the LIMIT statement to take a variable as one of it\'s parameters.

回答6:

Try this example.

create table student(id int, name varchar(30), age int);

insert into student values
(1 ,\'Ranga\', 27),
(2 ,\'Reddy\', 26),
(3 ,\'Vasu\',  50),
(5 ,\'Manoj\', 10),
(6 ,\'Raja\',  52),
(7 ,\'Vinod\', 27);

SELECT name,
       (SELECT name FROM student s1
        WHERE s1.id < s.id
        ORDER BY id DESC LIMIT 1) as previous_name,
       (SELECT name FROM student s2
        WHERE s2.id > s.id
        ORDER BY id ASC LIMIT 1) as next_name
FROM student s
    WHERE id = 7; 

Note: If value is not found then it will return null.

In the above example, Previous value will be Raja and Next value will be null because there is no next value.

回答7:

Next row

SELECT * FROM `foo` LIMIT number++ , 1

Previous row

SELECT * FROM `foo` LIMIT number-- , 1

sample next row

SELECT * FROM `foo` LIMIT 1 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 3 , 1

sample previous row

SELECT * FROM `foo` LIMIT -1 , 1
SELECT * FROM `foo` LIMIT -2 , 1
SELECT * FROM `foo` LIMIT -3 , 1

SELECT * FROM `foo` LIMIT 3 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 1 , 1

回答8:

Using @Dan \'s approach, you can create JOINs. Just use a different @variable for each sub query.

SELECT current_row.row, current_row.id, previous_row.row, previous_row.id
FROM (
  SELECT @rownum:=@rownum+1 row, a.* 
  FROM articles a, (SELECT @rownum:=0) r
  ORDER BY date, id
) as current_row
LEFT JOIN (
  SELECT @rownum2:=@rownum2+1 row, a.* 
  FROM articles a, (SELECT @rownum2:=0) r
  ORDER BY date, id
) as previous_row ON
  (current_row.id = previous_row.id) AND (current_row.row = previous_row.row - 1)

回答9:

I had the same problem as Dan, so I used his answer and improved it.

First select the row index, nothing different here.

SELECT row
FROM 
(SELECT @rownum:=@rownum+1 row, a.* 
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id) as article_with_rows
WHERE id = 50;

Now use two separate queries. For example if the row index is 21, the query to select the next record will be:

SELECT * 
FROM articles
ORDER BY date, id
LIMIT 21, 1

To select the previous record use this query:

SELECT * 
FROM articles
ORDER BY date, id
LIMIT 19, 1

Keep in mind that for the first row (row index is 1), the limit will go to -1 and you will get a MySQL error. You can use an if-statement to prevent this. Just don\'t select anything, since there is no previous record anyway. In the case of the last record, there will be next row and therefor there will be no result.

Also keep in mind that if you use DESC for ordering, instead of ASC, you queries to select the next and previous rows are still the same, but switched.

回答10:

This is universal solution for conditions wiht more same results.

<?php
$your_name1_finded=\"somethnig searched\"; //$your_name1_finded must be finded in previous select

$result = db_query(\"SELECT your_name1 FROM your_table WHERE your_name=your_condition ORDER BY your_name1, your_name2\"); //Get all our ids

$i=0;
while($row = db_fetch_assoc($result)) { //Loop through our rows
    $i++;
    $current_row[$i]=$row[\'your_name1\'];// field with results
    if($row[\'your_name1\'] == $your_name1_finded) {//If we haven\'t hit our current row yet
        $yid=$i;
    }
}
//buttons
if ($current_row[$yid-1]) $out_button.= \"<a  class=\'button\' href=\'/$your_url/\".$current_row[$yid-1].\"\'>BUTTON_PREVIOUS</a>\";
if ($current_row[$yid+1]) $out_button.= \"<a  class=\'button\' href=\'/$your_url/\".$current_row[$yid+1].\"\'>BUTTON_NEXT</a>\";

echo $out_button;//display buttons
?>

回答11:

How to get next/previous record in MySQL & PHP?

My example is to get the id only

function btn_prev(){

  $id = $_POST[\'ids\'];
  $re = mysql_query(\"SELECT * FROM table_name WHERE your_id < \'$id\'  ORDER BY your_id DESC LIMIT 1\");

  if(mysql_num_rows($re) == 1)
  {
    $r = mysql_fetch_array($re);
    $ids = $r[\'your_id\'];
    if($ids == \"\" || $ids == 0)
    {
        echo 0;
    }
    else
    {
        echo $ids;
    }
  }
  else
  {
    echo 0;
  }
}



function btn_next(){

  $id = $_POST[\'ids\'];
  $re = mysql_query(\"SELECT * FROM table_name WHERE your_id > \'$id\'  ORDER BY your_id ASC LIMIT 1\");

  if(mysql_num_rows($re) == 1)
  {
    $r = mysql_fetch_array($re);
    $ids = $r[\'your_id\'];
    if($ids == \"\" || $ids == 0)
    {
        echo 0;
    }
    else
    {
        echo $ids;
    }
  }
  else
  {
    echo 0;
  }
}

回答12:

Optimising @Don approach to use only One Query

SELECT * from (
  SELECT 
     @rownum:=@rownum+1 row,
     CASE a.id WHEN \'CurrentArticleID\' THEN @currentrow:=@rownum ELSE NULL END as \'current_row\',
     a.*  
  FROM articles a,
     (SELECT @currentrow:=0) c,  
     (SELECT @rownum:=0) r
   ORDER BY `date`, id  DESC
 ) as article_with_row
 where row > @currentrow - 2
 limit 3

change CurrentArticleID with current article ID like

SELECT * from (
  SELECT 
     @rownum:=@rownum+1 row,
     CASE a.id WHEN \'100\' THEN @currentrow:=@rownum ELSE NULL END as \'current_row\',
     a.*  
  FROM articles a,
     (SELECT @currentrow:=0) c,  
     (SELECT @rownum:=0) r
   ORDER BY `date`, id  DESC
 ) as article_with_row
 where row > @currentrow - 2
 limit 3

回答13:

Here we have a way to fetch previous and next records using single MySQL query. Where 5 is the id of current record.

select * from story where catagory=100 and  (
    id =(select max(id) from story where id < 5 and catagory=100 and order by created_at desc) 
    OR 
    id=(select min(id) from story where id > 5 and catagory=100 order by created_at desc) )

回答14:

There\'s another trick you can use to show columns from previous rows, using any ordering you want, using a variable similar to the @row trick:

SELECT @prev_col_a, @prev_col_b, @prev_col_c,
   @prev_col_a := col_a AS col_a,
   @prev_col_b := col_b AS col_b,
   @prev_col_c := col_c AS col_c
FROM table, (SELECT @prev_col_a := NULL, @prev_col_b := NULL, @prev_col_c := NULL) prv
ORDER BY whatever

Apparently, the select columns are evaluated in order, so this will first select the saved variables, and then update the variables to the new row (selecting them in the process).

NB: I\'m not sure that this is defined behaviour, but I\'ve used it and it works.

回答15:

If you want to feed more than one id to your query and get next_id for all of them...

Assign cur_id in your select field and then feed it to subquery getting next_id inside select field. And then select just next_id.

Using longneck answer to calc next_id:

select next_id
from (
    select id as cur_id, (select min(id) from `foo` where id>cur_id) as next_id 
    from `foo` 
) as tmp
where next_id is not null;

回答16:

CREATE PROCEDURE `pobierz_posty`(IN iduser bigint(20), IN size int, IN page int)
BEGIN
 DECLARE start_element int DEFAULT 0;
 SET start_element:= size * page;
 SELECT DISTINCT * FROM post WHERE id_users .... 
 ORDER BY data_postu DESC LIMIT size OFFSET start_element
END

回答17:

If you have an index column, say id, you can return previous and next id in one sql request. Replace :id with your value

SELECT
 IFNULL((SELECT id FROM table WHERE id < :id ORDER BY id DESC LIMIT 1),0) as previous,
 IFNULL((SELECT id FROM table WHERE id > :id ORDER BY id ASC LIMIT 1),0) as next

回答18:

My solution to get the next and previews record also to get back to the first record if i\'m by the last and vice versa

I\'m not using the id i\'m using the title for nice url\'s

I\'m using Codeigniter HMVC

$id = $this->_get_id_from_url($url);

//get the next id
$next_sql = $this->_custom_query(\"select * from projects where id = (select min(id) from projects where id > $id)\");
foreach ($next_sql->result() as $row) {
    $next_id = $row->url;
}

if (!empty($next_id)) {
    $next_id = $next_id;
} else {
    $first_id = $this->_custom_query(\"select * from projects where id = (SELECT MIN(id) FROM projects)\");
    foreach ($first_id->result() as $row) {
        $next_id = $row->url;
    }
}

//get the prev id
$prev_sql = $this->_custom_query(\"select * from projects where id = (select max(id) from projects where id < $id)\");
foreach ($prev_sql->result() as $row) {
    $prev_id = $row->url;
}

if (!empty($prev_id)) {
    $prev_id = $prev_id;
} else {
    $last_id = $this->_custom_query(\"select * from projects where id = (SELECT MAX(id) FROM projects)\");
    foreach ($last_id->result() as $row) {
        $prev_id = $row->url;
    }     
}

回答19:

Select top 1 * from foo where id > 4 order by id asc

回答20:

I think to have the real next or previous row in SQL table we need the real value with equal, (< or >) return more than one if you need to change position of row in a ordering table.

we need the value $position to search the neighbours row In my table I created a column \'position\'

and SQL query for getting the needed row is :

for next :

SELECT * 
FROM `my_table` 
WHERE id = (SELECT (id) 
            FROM my_table 
            WHERE position = ($position+1)) 
LIMIT 1

for previous:

 SELECT * 
 FROM my_table 
 WHERE id = (SELECT (id) 
             FROM my_table 
             WHERE `position` = ($position-1)) 
 LIMIT 1