samedi 27 juin 2015

SQL Check if value is beetween two different values from another table

I have a table like this in my Oracle database

ID
------
10 000
25 000
40 000
61 000
75 000

I need to check if value, for example 46 567, is between some of two neighbour values from this table.

46 567 is between 40 000 and 61 000, I should choose 40 000 because it is closer to 46 567 than 61 000.

Sure I can find top and low borders like this

--top border
SELECT MIN(ID) FROM (SELECT * FROM this_table WHERE ID > 46567);
--low border
SELECT MAX(ID) FROM (SELECT * FROM this_table WHERE ID < 46567);

But one cool pro told me I should perform some hierarchical query and It will looks really good, I don't see how can I use hierarchical query to solve this problem. It should looks like this Fibonacci SQL, but I can't implement this.

WITH
numbers (n, prev) AS (
SELECT 0 n, 1 prev from dual
UNION ALL
SELECT n + prev, n from numbers
WHERE n + prev < 50
)

Can you help me?

Aucun commentaire:

Enregistrer un commentaire