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