SQL query to collect rows above and below a certain match in Postgres

roku675 0
Viewed 25

I have a postgres table A where I am looking to query n rows above and below a certain chosen value in a column. I have already implemented two basic queries as shown below

    SELECT * FROM A 
    WHERE a >= 100 
    AND a <= 100+n

and

   SELECT * FROM A 
   WHERE a <= 100
   AND a >= 100-n

Is there a way to merge these two queries into 1 instead of having to define two separate queries?

postgresql
1 Answers

Naively just put both conditions in the where clause and link them with OR:

SELECT * FROM A 
WHERE ( a >= 100 AND a <= 100+n ) 
OR (a <= 100 AND a >= 100-n )

but that will probably perform badly.

with a union,

   SELECT * FROM A 
   WHERE a >= 100 
   AND a <= 100+n 
 UNION -- use UNION ALL here if you want two copies of all a=100 rows
   SELECT * FROM A 
   WHERE a <= 100
   AND a >= 100-n

Or by reasoning about ordered values and noting that the conditions specify ranges on a and noticing that they overlap.

 SELECT * FROM A 
 WHERE a >= 100-n
 AND a <= 100+n

this can also be rewritten as

 SELECT * FROM A 
 WHERE a BETWEEN 100-n AND 100+n
Jasen0
Related