CODEWITHSUNDEEP
sqlpostgresql
eumiro
eumiro

Reputation: 212835

Selecting numbers "larger" than a given number

I have a table in PostgreSQL with one number column and I have a given number x.

If x is in the table, I want all numbers >= x.

If x is not in the table, I want all numbers > x and the largest number < x.

Example:

id 
5
10
15
20

For x = 15 it should return 15 and 20.

For x = 12 it should return 10, 15 and 20.

I have tried the following:

SELECT id FROM table_name WHERE id > 12
UNION
SELECT MAX(id) FROM table_name WHERE id <= 12

which works correctly.

Is there any single-query way? Thank you.

(This is just an example with single column and numbers. The reality is a larger table and datetime column, but the principle should be the same.)

Upvotes: 3

Views: 10044

Answers (3)

PiyusG
PiyusG

Reputation: 1157

select * from A where id >= coalesce((select id from A where id = 13),(select id from A where id < 13 order by id desc limit 1));

select * from A where id >= coalesce((select id from A where id = 15),(select id from A where id < 15 order by id desc limit 1));

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

There is another way using windows functions:

select id
from (select id, lead(id) over (order by id) as nextid
      from t
     ) t
where nextid >= 12

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

Converted from my comment:

SELECT id 
  FROM table_name 
 WHERE id >= (SELECT MAX(id) 
                FROM table_name 
               WHERE id <= 12)

Upvotes: 4

Related Questions