SQL query to translate a list of numbers matched against several ranges, to a list of values
- by Claes Mogren
I need to convert a list of numbers that fall within certain ranges into a list of values, ordered by a priority column. The table has the following values:
| YEAR | R_MIN | R_MAX | VAL | PRIO |
------------------------------------
2010 18000 90100 52 6
2010 240000 240099 82 3
2010 250000 259999 50 5
2010 260000 260010 92 1
2010 330000 330010 73 4
2010 330011 370020 50 5
2010 380000 380050 84 2
The ranges will be different for different years. The ranges within one year will never overlap.
The input will be a year and a list of numbers that might fall within one these ranges. The list of input number will be small, 1 to 10 numbers. Example of input numbers:
(20000, 240004, 375000, 255000)
With that input I would like to get a list ordered by the priority column, or a single value:
82
50
52
The only value I'm interested in here is 82, so UNIQUE and MAX_RESULTS=1 would do. It can easily be done with one query per number, and then sorting it in the Java code, but I would prefer to do it in a single SQL query.
What SQL query, to be run in an Oracle database, would give me the desired result?
