Question 90081 in PostgreSQL Question posted in
The official documentation can be found here.

How to retrieve the N first rows AND the N last rows in only one request? – Postgresql

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.

Tags:

2

Answers


  1. 0 
    (SELECT * FROM table_name LIMIT 5) UNION (SELECT * FROM table_name ORDER BY id DESC LIMIT 5);
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search