Select adjacent records in Mysql

Question

Assuming this table is ordered by date

id      | date      | customer
3       | 2009-10-01| Frank
1       | 2010-10-11| Bob
4       | 2010-11-01| Mitchel
2       | 2010-11-02| Jim

I would like to make a query so that knowing ID = 4 the resulting rows are

$row[0]['id'] == 1 //previous
$row[1]['id'] == 4 //most recent/current
$row[2]['id'] == 2 //next

A mysql only solution would be best, but if there is an elegant php solution that would be cool as well.

Answer 1

As the table IS sorted by date column, you can run following queries to get it:

• For previous row:

  select * from tablename where `date` < (select `date` from tablename where id=4) order by `date` desc limit 1
  

• For current row:

  select * from tablename where id=4
  

• For next row:

  select * from tablename where `date` > (select `date` from tablename where id=4) order by `date` asc limit 1
  

---

Output: These three queries return the result (one by one) as following:

id  date       customer
1   2010-10-11  Bob
4   2010-11-01  Mitchel
2   2010-11-02  Jim

Answer 2

Since you are ordering by date, but basing the row you want the adjacent rows on id, your going to have to do 2 queries. The first to determine the date for the ID you have selected, the second to get the adjacent rows.

Step 1 - Get the date

Select date
FROM   yourtable
WHERE id = 4

Step 2 - Get all the rows

SELECT *
FROM   yourtable
WHERE date IN (  (select MAX( date ) from yourtable where date < $datefromquery1)
                 , $datefromquery1
                 , (select MIN( date ) from yourtable where date > $datefromquery1)
                )