CODEWITHSUNDEEP
sqlpostgresqlsqlite
user10181387
user10181387

Reputation: 3

SQL - Getting two rows that contain value in between them

Assume I have a table like so:

A | B
1 | something
2 | something2
4 | something3
8 | something4

It's always in order by A, but they're not necessarily contiguous.

Now let's say I input the number 6, what I want to be returned is:

A | B
4 | something3
8 | something4

Because 6 is between 4 and 8.

And if I search for 2 then I just want that 2nd row. Or actually both the 2nd and 3rd or 1st and 2nd is fine, doesn't matter, that filtering can be done easily.

Edge cases don't matter much either, but let's say we have 0 I want just the 1st row, and let's say 12 I want just the last row, but again if it returns nothing that's fine too.

One option I have is to store them as ranges, i.e.:

A | B    | C
1 | 2    | something
2 | 4    | something2
4 | 8    | something3
8 | null | something4

...and then looking up will be easy, but I'm trying to avoid that. I can't really think of a way to do this contiguous rows comparison.

I'm using PSQL but I'm more curious if there's a way to do this in SQLite as well.

Upvotes: 0

Views: 60

Answers (1)

Imbar M.
Imbar M.

Reputation: 1114

this will get you the first row greater than 6, the first row lower than 6 and the row 6 (assuming the input is 6).

you could play with this. maybe running the last part first and if you don't get any results that run the union of the first and the second to get the surrounding rows. 

select *
from t
where A > 6
order by A asc
limit 1

union

select *
from t
where A < 6
order by A desc
limit 1

union

select *
from t
where A = 6

Upvotes: 1

Related Questions