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.

this becomes more complex when used against a table with many columns, and you want to select *. And if there can be duplicate values in the column you are "paging" on, the complexity increases again. — TheSoftwareJedi, Oct 15 '08 at 00:22
uh huh, like I said, you had to go and make it more complex, didn't you? :) — TheSoftwareJedi, Oct 15 '08 at 01:44
I'm trying to do the [same thing via Entity Framework](http://stackoverflow.com/questions/5382023/how-to-select-the-next-and-previous-entity-from-a-table). — Drew Noakes, Mar 21 '11 at 18:37
Just to clarify, you ONLY want 3 rows returned (in your example)? One above, one below and the one supplied? — Mark, Oct 15 '08 at 00:17

fatbuddha · Answer 1 · 2008-10-15T00:25:43.220

10

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.

edited Oct 15 '08 at 00:25

answered Oct 15 '08 at 00:16

fatbuddha

987
1
6
8

Cade Roux · Answer 2 · 2008-10-15T02:16:32.137

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).

TheSoftwareJedi · Answer 3 · 2008-10-15T01:51:18.180

Firstly, this should work (the ORDER BY is important):

select min(a)
from theTable
where a > 8

select max(a)
from theTable
where a < 8

For the second question that I begged you to ask...:

    select * 
    from theTable
    where date = 8

    union all

    select *
    from theTable
    where key = (select min(key) 
                 from theTable
                 where key > (select max(key)
                              from theTable
                              where date = 8)
                )

    union all

    select *
    from theTable
    where key = (select max(key) 
                 from theTable
                 where key < (select min(key)
                              from theTable
                              where date = 8)
                )

    order by key

score 1 · Answer 4 · answered Oct 15 '08 at 01:04

SELECT 'next' AS direction, MIN(date_field) AS date_key
FROM table_name
  WHERE date_field > current_date
GROUP BY 1 -- necessity for group by varies from DBMS to DBMS in this context
UNION
SELECT 'prev' AS direction, MAX(date_field) AS date_key
  FROM table_name
  WHERE date_field < current_date
GROUP BY 1
ORDER BY 1 DESC;

Produces:

direction  date_key
---------  --------
prev              3
next             19

John McAleely · Accepted Answer · 2008-10-15T12:41:23.467

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.

score 1 · Answer 6 · answered Nov 27 '11 at 20:28

1

If your RDBMS supports LAG and LEAD, this is straightforward (Oracle, PostgreSQL, SQL Server 2012)

These allow to choose the row either side of any given row in a single query

answered Nov 27 '11 at 20:28

gbn

422,506
82
585
676

score 0 · Answer 7 · answered Oct 15 '08 at 01:39

0

Try this...

SELECT TOP 3 * FROM YourTable
WHERE Col >= (SELECT MAX(Col) FROM YourTable b WHERE Col < @Parameter)
ORDER BY Col

answered Oct 15 '08 at 01:39

Leon Tayson

4,741
7
37
36

This doesn't seem to work at the start of the sequence - returning no results when 1 is the parameter value. It did work for 67 at the end though. – John McAleely Oct 15 '08 at 12:13

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

7 Answers7

Linked

Related