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