Extracting rows from SQL query given a certain value-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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