How to get next previous record in MySQL

Question

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

User · Answer

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  gt  ID   union select max ns UserID  from Users ns where  ns UserID  lt  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  lt  ID   ORDER BY ns UserID DESC LIMIT 1  END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt cur id  as next id      from  foo     as tmp where next id is not null

User · Answer

In addition to cemkalyoncu s solution   next record   SELECT   FROM foo WHERE id  gt  4 ORDER BY id LIMIT 1    previous record   SELECT   FROM foo WHERE id  lt  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

User · Answer

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

User · Answer

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  lt  5 and catagory 100 and order by created at desc       OR      id  select min id  from story where id  gt  5 and catagory 100 order by created at desc

User · Answer

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  lt   id ORDER BY id DESC LIMIT 1  0  as previous   IFNULL  SELECT id FROM table WHERE id  gt   id ORDER BY id ASC LIMIT 1  0  as next

User · Answer

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  lt  s id         ORDER BY id DESC LIMIT 1  as previous name          SELECT name FROM student s2         WHERE s2 id  gt  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

User · Answer

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  gt  4  0           or  id   IFNULL  select max id  from foo where id  lt  4  0

User · Answer

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

User · Answer

I think to have the real next or previous row in SQL table we need the real value with equal    lt  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

User · Answer

This is universal solution for conditions wiht more same results    lt  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     lt a  class  button  href    your url    current row  yid-1     gt BUTTON PREVIOUS lt  a gt    if   current row  yid 1    out button     lt a  class  button  href    your url    current row  yid 1     gt BUTTON NEXT lt  a gt     echo  out button   display buttons   gt

User · Answer

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- gt  get id from url  url      get the next id  next sql    this- gt  custom query  select   from projects where id    select min id  from projects where id  gt   id     foreach   next sql- gt result   as  row         next id    row- gt url     if   empty  next id          next id    next id    else        first id    this- gt  custom query  select   from projects where id    SELECT MIN id  FROM projects         foreach   first id- gt result   as  row             next id    row- gt url             get the prev id  prev sql    this- gt  custom query  select   from projects where id    select max id  from projects where id  lt   id     foreach   prev sql- gt result   as  row         prev id    row- gt url     if   empty  prev id          prev id    prev id    else        last id    this- gt  custom query  select   from projects where id    SELECT MAX id  FROM projects         foreach   last id- gt result   as  row             prev id    row- gt url

User · Answer

next   select   from foo where id    select min id  from foo where id  gt  4    previous   select   from foo where id    select max id  from foo where id  lt  4

User · Answer

100   working  SELECT   FROM  table  where table id  lt  3 ORDER BY  table id  DESC limit 1

User · Answer

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  gt   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  gt   currentrow - 2  limit 3

User · Answer

SELECT   FROM foo WHERE id gt 4 ORDER BY id LIMIT 1

User · Answer

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

User · Answer

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

User · Answer

How to get next previous record in MySQL  amp  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  lt    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  gt    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

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

Examples related to sql

Examples related to mysql