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

The LIMIT function can take two arguments, an offset and a number of rows to return. In your case, you want the offset to be (the number of rows with dates before the desired row) - 1, or in this case 2 - 1 = 1, and the number of rows to be three. So the SQL you want is

SELECT * FROM customers ORDER BY date ASC LIMIT 1,3;

and the number "1" will be the result of the query

SELECT COUNT(*)-1 FROM customers WHERE date > "2010-11-01";

I don't believe MySQL will let you use a subselect or function value as the argument of LIMIT, so you'll have to store that using PHP and construct the next query that way.

Answer 3

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