Home > database >  How to retrieve the N first rows AND the N last rows in only one request?
How to retrieve the N first rows AND the N last rows in only one request?

Time:01-19

Let's say we have a huge query like this:

SELECT id, quality FROM products ORDER BY quality

Is it possible to retrieve the N first rows AND the N last rows of the results, without performing two requests ?

What I want to avoid (two requests):

SELECT id, quality FROM products ORDER BY quality LIMIT 5;
SELECT id, quality FROM products ORDER BY quality DESC LIMIT 5;

Context: the actual request is very CPU/time consuming, that's why I want to limit to one request if possible.

CodePudding user response:

Using a WITH clause to avoid writing the same code twice:

WITH my_complex_query AS (
    SELECT * FROM table_name
)
(SELECT * FROM my_complex_query ORDER BY id ASC LIMIT 5)
UNION ALL
(SELECT * FROM my_complex_query ORDER BY id DESC LIMIT 5)

CodePudding user response:

(SELECT * FROM table_name LIMIT 5) UNION (SELECT * FROM table_name ORDER BY id DESC LIMIT 5);
  • Related