sql

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


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.


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.