CODEWITHSUNDEEP
mysqlselect
LowLevel
LowLevel

Reputation: 1095

What optimal selection statements are possible to select previous and next rows (in one statement)?

How to select previous AND next rows from an ordered table, ordered by an order column?

This is a simple example of such a table (e.g. test_table):

+--------+-----------+----------+
| id     | name      | order    |
+--------+-----------+----------+
|    126 | Test 0    |        0 |
+--------+-----------+----------+
|     73 | Test 1    |        1 | >
+--------+-----------+----------+
|    801 | Test 5    |        5 | <<<
+--------+-----------+----------+
|      3 | Test 8    |        8 | >
+--------+-----------+----------+
|     45 | Test 12   |       12 |
+--------+-----------+----------+

This is an example statement, that makes it possible to do what I need (in this example I have the order-value 5, and I need the previous and next rows by order):

SELECT * FROM
    (
        SELECT * FROM test_table
        WHERE test_table.order < 5
        ORDER BY test_table.order DESC LIMIT 1
    ) AS a

UNION

SELECT * FROM
    (
        SELECT * FROM test_table
        WHERE test_table.order > 5
        ORDER BY test_table.order LIMIT 1
    ) AS b

However, I think it is too complicated. Is there another way to do it, using less selects (and/or without a union)? In short: are there more performant/efficient and optimal statements/algorithms or some best practices?

To be clearer, I expect the following result set:

+--------+-----------+----------+
| id     | name      | order    |
+--------+-----------+----------+
|     73 | Test 1    |        1 |
+--------+-----------+----------+
|      3 | Test 8    |        8 |
+--------+-----------+----------+

P.S. Please, do not use any procedures or custom functions. Assume that there are no appropriate administrator rights for it.

Upvotes: 1

Views: 36

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

One alternative way to do this would be to use the LEAD and LAG analytic functions:

WITH cte1 AS (
    SELECT id,
        LAG(id) OVER (ORDER BY `order`) id_lag,
        LEAD(id) OVER (ORDER BY `order`) id_lead
    FROM test_table
),
cte2 AS (
    SELECT * FROM cte1 WHERE `order` = 5
)

SELECT id, name, `order` FROM test_table WHERE id = (SELECT id_lag FROM cte2)
UNION ALL
SELECT id, name, `order` FROM test_table WHERE id = (SELECT id_lead FROM cte2);

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31832

As I see, your solution is already optimal. I would though write it a bit shorter and use UNION ALL instead of UNION (which is a shortcut for UNION DISTINCT):

(
    SELECT * FROM test_table
    WHERE test_table.order < 5
    ORDER BY test_table.order DESC LIMIT 1
) UNION ALL (
    SELECT * FROM test_table
    WHERE test_table.order > 5
    ORDER BY test_table.order LIMIT 1
)

Given an index on the order column, it should also have the best possible performance.

Upvotes: 1

Related Questions