grab previous and next date

[cgraz]

I'm trying to figure out how to grab both the previous and next date (if they exist) in MySQL based on the date of the current ID. There won't always be a record for each month, otherwise I could test a range +/- one month.

Relevant fields are id and release_date

I can use a self join to grab either previous or next in separate queries, but I'd like to do so with only one query and grab both. Here's what I have for previous date.

SELECT e1.id, e1.release_date
FROM our_desk AS e1, our_desk AS e2
WHERE e2.id = 5
AND e1.release_date < e2.release_date ORDER BY e1.release_date DESC 
LIMIT 1 

[Shrike]

I think this should be possible using subqueries, should your database support them. If not it might be possible to replace with left joins. I don't know the syntax for MySQL subqueries so I've used MSSQL 🙂

The inner most subquery gets the date of ID 5, the middle subquery gets the date of the record greater than ID 5, the outer most query grabs the 3 records with a date less than or equal to the subquery return value. The ordering should ensure the records are in the right order.

What would happen if you reach the boundary of the records, i.e. there are no records in the future/past?

# Get the 3 records with a date less than subquery return
SELECT id, release_date FROM our_desk
WHERE release_date <= (
  # Get the next 1 record with a date after ID 5
  SELECT release_date FROM our_desk WHERE release_date > (
    # Get the date of ID 5
    SELECT release_date FROM our_desk WHERE id = 5
  )
  LIMIT 1
  ORDER BY release_date
)
ORDER BY release_date DESC
LIMIT 3

Interestingly this is the sort of technique used in MSSQL to do recordset paging, since there is no LIMIT clause 🙂