[sql] How to get next/previous record in MySQL?

Say I have records with ID 3,4,7,9

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.

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;
    }     
}

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

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.

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)

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.

Here is my answer. I pick up an idea from 'Decent Dabbler' and add the part that is checking if id is between min(id) and max(id). Here is the part for creating my table.

CREATE TABLE Users (
UserID int NOT NULL auto_increment,
UserName varchar(45),
UserNameID varchar(45),
PRIMARY KEY (UserID)

);

Next step is creating a stored procedure that is responsible for getting the previous id.

    CREATE DEFINER=`root`@`localhost` PROCEDURE `printPreviousIDbySelectedIDUser`(
IN ID int,
IN search_name varchar(45)
)
BEGIN
SELECT CONCAT(ns.UserID) AS 'Previous ID' from Users ns
 where ns.UserName=search_name AND ns.UserID IN (select min(ns.UserID) from Users ns where ns.UserID > ID 
 union select max(ns.UserID) from Users ns where  ns.UserID < ID) LIMIT 1 ;
END

The first method is good if the indexes are sorted, but if they are not. For example, if you have indexes: 1,2,7 and you need to get index number 2 in this way much better to use another approach.

    CREATE DEFINER=`root`@`localhost` PROCEDURE `getPreviousUserID`(
IN ID int,
IN search_name varchar(45)
)
BEGIN
SELECT CONCAT(ns.UserID) AS 'Previous ID' from Users ns
  WHERE ns.UserName=search_name AND  ns.UserID < ID   ORDER BY ns.UserID DESC LIMIT 1;
END

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

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

100 % working

SELECT * FROM `table` where table_id < 3 ORDER BY `table_id` DESC limit 1

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.

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

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

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;
  }
}

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

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;

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.

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

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

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

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

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.