Select adjacent records in Mysql
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 开发者_Python百科would be cool as well.
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
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)
)
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.
精彩评论