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

Question

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.

Answer 1

Select max(element) From Data Where Element < 8

Union

Select min(element) From Data Where Element > 8

But generally it is more usefull to think of sql for set oriented operations rather than iterative operation.

Answer 2

Self-joins.

For the table:

/*
CREATE TABLE [dbo].[stackoverflow_203302](
    [val] [int] NOT NULL
) ON [PRIMARY]
*/

With parameter @val

SELECT cur.val, MAX(prv.val) AS prv_val, MIN(nxt.val) AS nxt_val
FROM stackoverflow_203302 AS cur
LEFT JOIN stackoverflow_203302 AS prv
    ON cur.val > prv.val
LEFT JOIN stackoverflow_203302 AS nxt
    ON cur.val < nxt.val
WHERE cur.val = @val
GROUP BY cur.val

You could make this a stored procedure with output parameters or just join this as a correlated subquery to the data you are pulling.

Without the parameter, for your data the result would be:

val         prv_val     nxt_val
----------- ----------- -----------
1           NULL        3
3           1           8
8           3           19
19          8           45
45          19          67
67          45          NULL

For the modified example, you use this as a correlated subquery:

/*
CREATE TABLE [dbo].[stackoverflow_203302](
    [ky] [int] NOT NULL,
    [val] [int] NOT NULL,
    CONSTRAINT [PK_stackoverflow_203302] PRIMARY KEY CLUSTERED (
        [ky] ASC
    )
)
*/

SELECT cur.ky AS cur_ky
        ,cur.val AS cur_val
        ,prv.ky AS prv_ky
        ,prv.val AS prv_val
        ,nxt.ky AS nxt_ky
        ,nxt.val as nxt_val
FROM (
    SELECT cur.ky, MAX(prv.ky) AS prv_ky, MIN(nxt.ky) AS nxt_ky
    FROM stackoverflow_203302 AS cur
    LEFT JOIN stackoverflow_203302 AS prv
        ON cur.ky > prv.ky
    LEFT JOIN stackoverflow_203302 AS nxt
        ON cur.ky < nxt.ky
    GROUP BY cur.ky
) AS ordering
INNER JOIN stackoverflow_203302 as cur
    ON cur.ky = ordering.ky
LEFT JOIN stackoverflow_203302 as prv
    ON prv.ky = ordering.prv_ky
LEFT JOIN stackoverflow_203302 as nxt
    ON nxt.ky = ordering.nxt_ky

With the output as expected:

cur_ky      cur_val     prv_ky      prv_val     nxt_ky      nxt_val
----------- ----------- ----------- ----------- ----------- -----------
1           1           NULL        NULL        2           3
2           3           1           1           3           8
3           8           2           3           4           19
4           19          3           8           5           67
5           67          4           19          6           45
6           45          5           67          NULL        NULL

In SQL Server, I prefer to make the subquery a Common table Expression. This makes the code seem more linear, less nested and easier to follow if there are a lot of nestings (also, less repetition is required on some re-joins).