Extracting rows from SQL query given a certain value

Question

The column c is in ascending order as you can see below.

Let's say I have a value x = 25; I want to extract the rows between which 25 lies corresponding to the c column. Clearly 25 lies between rows 2 and 3.

How can I extract rows 2 and 3 from a SQL query?

a	b	c
100	200	5
700	2000	20
600	110	100

Answer 1

Find all the values less or equal to 25. Then order them by c and take the first.

Do it again for c greater than 25.

You can do two queries and union them.

select *
from table
where c <= 25
order by c desc
limit 1

union

select *
from table
where c > 25
order by c asc
limit 1

Answer 2

Use it.

WITH temp as (SELECT a, b, c
              , min( CASE WHEN 25 < c THEN c ELSE NULL END ) OVER ( ORDER BY c )      AS col1
              , max( CASE WHEN 25 < c THEN NULL ELSE c END ) OVER ( ORDER BY c DESC ) AS col2
         FROM data
         ORDER BY c)
SELECT a,b,c
FROM temp t
WHERE t.col1 = t.c
   OR t.col2 = t.c

Also, if you don't want to pass 25 as parameter you can add it to table/view as column

test it

Answer 3

Use NOT EXISTS:

SELECT t1.*
FROM tablename t1
WHERE NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.c <> t1.c
              AND (t2.c BETWEEN t1.c AND ? OR t2.c BETWEEN ? AND t1.c)
          );

Replace ? with the value that you want.

Results for ? = 25:

a	b	c
700	2000	20
600	110	100

Results for ? = 20:

a	b	c
700	2000	20

Results for ? = 4:

a	b	c
100	200	5

Results for ? = 110:

a	b	c
600	110	100

See the demo.