How to take the near number in a column at Oracle Select.

Question

Hello Folks,

I have an adress table and I need to take the near number. For example if I´m entenring the number 256 in this case I´ll take 257 because: 254<--256->257

Somebody knows the solution.

Thanks and sorry for my bad English.

Answer 1

The distance between 257 and the number is abs(number-257). So you can find (one of) the nearest numbers with:

select number
from (
    select number
    from yourtable
    order by abs(number-257)
) sub
where rownum < 2

Answer 2

SELECT  *
FROM    mytable
WHERE   mynumber BETWEEEN 256 - 2 AND 256 + 2

If you just need to pick the first match, use this:

SELECT  *
FROM    (
        SELECT  *
        FROM    (
                SELECT  *
                FROM    (
                        SELECT  *
                        FROM    mytable
                        WHERE   mynumber <= 256
                        ORDER BY
                                mynumber DESC
                        )
                WHERE   rownum = 1
                UNION ALL
                SELECT  *
                FROM    (
                        SELECT  *
                        FROM    mytable
                        WHERE   mynumber > 256
                        ORDER BY
                                mynumber
                        )
                WHERE   rownum = 1
                )
        ORDER BY
                ABS(256 - number), mynumber DESC
        )
WHERE   rownum = 1

This is more index efficient, since the final ORDER BY will sort at most two records.

Answer 3

I tried the sql in other way but based on the Quassnoi´s SQL. I´ve made some revisions and it´s working well. Look:

(SELECT * FROM 
  (SELECT number 
    FROM numberList 
    WHERE number < ? 
    ORDER BY number DESC) 
    WHERE ROWNUM=1) 
    UNION ALL 
    (SELECT * FROM 
    (SELECT number  
    FROM numberList 
    WHERE numeber > ? 
    ORDER BY number ASC) 
    WHERE ROWNUM=1)

Answer 4

Try this

Num integer := 236;

 select Max(number) AS NearestAndGreatest  from mytbl t
 join(
 select MIN(T.distance) AS dist from(
 select number,abs(number-Num) AS distance from mytbl)T)X
 on abs(number-Num) = X.dist