What is the SQL for 'next' and 'previous' in a table?

sql

Solution

My own attempt at the set solution, based on TheSoftwareJedi.

First question:

select date from test where date = 8
union all
select max(date) from test where date < 8
union all
select min(date) from test where date > 8
order by date;

Second question:

While debugging this, I used the data set:

(key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8,17:3,18:1

to give this result:

select * from test2 where date = 8
union all
select * from (select * from test2
                   where date = (select max(date) from test2 
                                     where date < 8)) 
    where key = (select max(key) from test2 
                    where date = (select max(date) from test2 
                                      where date < 8))
union all
select * from (select * from test2
                   where date = (select min(date) from test2 
                                     where date > 8)) 
    where key = (select min(key) from test2 
                    where date = (select min(date) from test2 
                                      where date > 8))
order by date,key;

In both cases the final order by clause is strictly speaking optional.

Problem

I have a table of items, each of which has a date associated with it. If I have the date associated with one item, how do I query the database with SQL to get the 'previous' and 'subsequent' items in the table? It is not possible to simply add (or subtract) a value, as the dates do not have a regular gap between them. One possible application would be 'previous/next' links in a photo album or blog web application, where the underlying data is in a SQL table. I think there are two possible cases: Firstly where each date is unique: Sample data: ``` 1,3,8,19,67,45 ``` What query (or queries) would give 3 and 19 when supplied 8 as the parameter? (or the rows 3,8,19). Note that there are not always three rows to be returned - at the ends of the sequence one would be missing. Secondly, if there is a separate unique key to order the elements by, what is the query to return the set 'surrounding' a date? The order expected is by date then key. Sample data: ``` (key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8 ``` What query for '8' returns the set: ``` 2:3,3:8,4:8,16:8,5:19 ``` or what query generates the table: ``` key date prev-key next-key 1 1 null 2 2 3 1 3 3 8 2 4 4 8 3 16 5 19 16 10 10 19 5 11 11 67 10 15 15 45 11 null 16 8 4 5 ``` The table order is not important - just the next-key and prev-key fields. Both TheSoftwareJedi and Cade Roux have solutions that work for the data sets I posted last night. For the second question, both seem to fail for this dataset: ``` (key:date) 1:1,2:3,3:8,4:8,5:19,10:19,11:67,15:45,16:8 ``` The order expected is by date then key, so one expected result might be: ``` 2:3,3:8,4:8,16:8,5:19 ``` and another: ``` key date prev-key next-key 1 1 null 2 2 3 1 3 3 8 2 4 4 8 3 16 5 19 16 10 10 19 5 11 11 67 10 15 15 45 11 null 16 8 4 5 ``` The table order is not important - just the next-key and prev-key fields.

Original source