What Is the SQL for 'Next' and 'Previous' in a Table

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

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.

Get previous and next row from rows selected with (WHERE) conditions

you didn't specify your DBMS, so the following is ANSI SQL:

select prev_word, word, next_word
from (
    select id, 
           lag(word) over (order by id) as prev_word,
           word,
           lead(word) over (order by id) as next_word
    from words
) as t
where word = 'name';

SQLFiddle: http://sqlfiddle.com/#!12/7639e/1

Get next and previous row in database table using two fields

Here is one method. For the previous record:

select t.*
from t cross join
     (select t.* from t where t.record = ?) tt
where t.date < tt.date or
      t.date = tt.date and t.shift < tt.shift
order by t.date desc, tt.shift desc
fetch first 1 row only;

For the next record:

select t.*
from t cross join
     (select t.* from t where t.record = ?) tt
where t.date > tt.date or
      t.date = tt.date and t.shift > tt.shift
order by t.date desc, tt.shift asc
fetch first 1 row only;

Note that fetch first 1 row only retrieves one record in ANSI/ISO SQL. Some databases use other syntax, such as limit or selecct top (1).

How to get next/previous record in MySQL?

select * from foo where id = (select min(id) from foo where id > 4)

select * from foo where id = (select max(id) from foo where id < 4)

order by next and previous

Hi i found a solution with procedure.
when i have such table:

CREATE TABLE `zz_test` (
  `id` int(11) NOT NULL,
  `prev` int(11) DEFAULT NULL,
  `next` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

and the records:

INSERT INTO `zz_test` VALUES ('1', '7', '2');
INSERT INTO `zz_test` VALUES ('2', '1', '3');
INSERT INTO `zz_test` VALUES ('3', '2', '6');
INSERT INTO `zz_test` VALUES ('4', '6', '5');
INSERT INTO `zz_test` VALUES ('5', '4', null);
INSERT INTO `zz_test` VALUES ('6', '3', '4');
INSERT INTO `zz_test` VALUES ('7', null, '1');

it can be sorted by this procedure:

DROP PROCEDURE IF EXISTS zz_sort_test;
CREATE PROCEDURE zz_sort_test()
BEGIN

    DECLARE next_value INT DEFAULT 1;
    DECLARE rows_count INT DEFAULT 0;
    DECLARE iterations INT DEFAULT 0;

    DECLARE cid, cnext, cprev INT DEFAULT NULL;

    DROP TEMPORARY TABLE IF EXISTS sorted;
    CREATE TEMPORARY TABLE IF NOT EXISTS sorted (iter INT, id INT, prev INT, next INT);

    SELECT COUNT(1) INTO rows_count FROM zz_test;

    #first row
    SELECT next INTO next_value FROM zz_test WHERE prev IS NULL LIMIT 1;
    SELECT id, prev, next INTO cid, cprev, cnext FROM zz_test WHERE next_value = next;
    INSERT INTO sorted (iter, id, prev, next) VALUES (-1, cid, cprev, cnext);

    loopy: LOOP
        SET iterations = iterations + 1;
        IF rows_count = iterations THEN
            LEAVE loopy;
        END IF;

        SELECT id, prev, next INTO cid, cprev, cnext FROM zz_test WHERE next_value = id;
        SET next_value := cnext;

        INSERT INTO sorted (iter, id, prev, next) VALUES (iterations, cid, cprev, cnext);
    END LOOP loopy;

    SELECT id, prev, next FROM sorted ORDER BY iter;

END

to explain what is happening in there:

it creates temporary table for storing the selected data (sorted)
selects count of rows from zz_test table (rows_count)
selects minimal (without null) next value (next_value) and inserts row with it into temp table
in the loop is saving next_value for next iteration to search by
and number of iterations to know when to stop (iterations)
rows into temporary table are inserted with iteration counter because at the end it will help with the sort
at the end, simple return all sorted from temp table

you can call it by:

call zz_sort_test;

i tested it with mysql 5.7.18

EDIT:
i edited the answer, so first selected row will be one with prev value = NULL
i added LIMIT 1 for situations where there will be more than one row with prev = NULL

How to get previous/next row when ordering by date/time then ID in MySQL?

Method #1 (requires MySQL 8+):

SQL

-- Previous ID
WITH cte_desc AS (SELECT * FROM `table` ORDER BY `date` DESC, id DESC),
     cte_r AS (SELECT * FROM `table` WHERE id = @r_id)
SELECT id AS prev_id
FROM cte_desc
WHERE `date` < (SELECT `date` FROM cte_r)
   OR `date` = (SELECT `date` FROM cte_r) AND id < (SELECT id FROM cte_r)
LIMIT 1;

-- Next ID
WITH cte_asc AS (SELECT * FROM `table` ORDER BY `date`, id),
     cte_r AS (SELECT * FROM `table` WHERE id = @r_id)
SELECT id AS next_id
FROM cte_asc
WHERE `date` > (SELECT `date` FROM cte_r)
   OR `date` = (SELECT `date` FROM cte_r) AND id > (SELECT id FROM cte_r)
LIMIT 1;

where @r_id is set to the ID of the row you want to find the previous/next for = 8 in your example.

Explanation

Two Common Table Expressions are defined: cte_desc sorts the table and cte_r gets the current row. The query part then finds the top row for which either the date value is strictly less than that of the chosen row or for which it is equal but the id is strictly less.

Online Demo

Dbfiddle demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5380e374f24243d578db28b9f89b9c8c

Method #2 (for earlier MySQL versions)

Similar to above - just slightly longer when there is no CTE support:

SQL

-- Previous ID
SELECT id AS prev_id
FROM (SELECT * FROM `table` ORDER BY `date` DESC, id DESC) sub
WHERE `date` < (SELECT `date` FROM `table` WHERE id = @r_id)
   OR `date` = (SELECT `date` FROM `table` WHERE id = @r_id)
      AND id < (SELECT id FROM `table` WHERE id = @r_id)
LIMIT 1;

-- Next ID
SELECT id AS next_id
FROM (SELECT * FROM `table` ORDER BY `date`, id) sub
WHERE `date` > (SELECT `date` FROM `table` WHERE id = @r_id)
   OR `date` = (SELECT `date` FROM `table` WHERE id = @r_id)
      AND id > (SELECT id FROM `table` WHERE id = @r_id)
LIMIT 1;

Online Demo

Rextester demo: https://rextester.com/MTW78358

Method #3 (Slower? See first comments):

-- Previous ID
SELECT id AS prev_id
FROM `table`
WHERE CONCAT(`date`, LPAD(id, 8, '0')) =
(SELECT MAX(CONCAT(`date`, LPAD(id, 8, '0')))
 FROM `table`
 WHERE CONCAT(`date`, LPAD(id, 8, '0')) < (SELECT CONCAT(`date`, LPAD(id, 8, '0')) 
                                           FROM `table`
                                           WHERE id = @r_id));
-- Next ID
SELECT id AS next_id
FROM `table`
WHERE CONCAT(`date`, LPAD(id, 8, '0')) =
(SELECT MIN(CONCAT(`date`, LPAD(id, 8, '0')))
 FROM `table`
 WHERE CONCAT(`date`, LPAD(id, 8, '0')) > (SELECT CONCAT(`date`, LPAD(id, 8, '0'))
                                           FROM `table`
                                           WHERE id = @r_id));

Online Demo

Rextester demo: https://rextester.com/BSQQL24519

Explanation

The ordering is by date/time then by ID so to simplify the searching, these are concatenated into a single string - but there is the usual snag of a string ordering placing e.g. 10 after 1 rather than after 9. To overcome this, the IDs are padded with zeros up to the number of digits of the maximum integer in MySQL (4294967295) - using the LPAD function. Having done this groundwork, the previous row can then be found by looking for the largest one that is less than the one for the current id value using MAX and a subselect.

How to select first, last and previous and next via sql

SELECT
  *
FROM
(
  SELECT
    MIN(fruit)   as first,
    MAX(fruit)   as previous
  FROM
    yourTable
  WHERE
    fruit < 'banana'
)
CROSS JOIN
(
  SELECT
    MIN(fruit)   as next,
    MAX(fruit)   as last
  FROM
    yourTable
  WHERE
    fruit > 'banana'
)

Or, shorter, but I'm not sure that it would be more performant...

SELECT
  MIN(CASE WHEN fruit < 'banana' THEN fruit END)   as first,
  MAX(CASE WHEN fruit < 'banana' THEN fruit END)   as previous,
  MIN(CASE WHEN fruit > 'banana' THEN fruit END)   as next,
  MAX(CASE WHEN fruit > 'banana' THEN fruit END)   as last
FROM
  yourTable

Or, as a final wierd option...

SELECT
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit < 'banana' ORDER by fruit ASC)  AS first,
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit < 'banana' ORDER by fruit DESC) AS previous,
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit > 'banana' ORDER by fruit ASC)  AS next,
  (SELECT TOP 1 fruit FROM yourTable WHERE fruit > 'banana' ORDER by fruit DESC) AS last

(This may be usefull if you have thousands+ of records, as each is search quickly and separately.)

What Is the SQL for 'Next' and 'Previous' in a Table