Reputation: 691
I have an oracle table with the columns Config_ID, Escalation_Level
In this table the Escalation_Level is a Varchar with the values 'L0','L1','L2','L13','L4','L3','L5','L22','L19','L41''L98','L99' in jumbled order for a Config_ID.
How to find the Nth MIN of the Escalation_Level. As this is of Varchar type, I don't find a straight forward way.
Please share your thoughts.
Regards, Sriram
Upvotes: 0
Views: 116
Reputation: 52923
If you want to find the Nth value of anything then the analytic function NTH_VALUE()
is a good place to start.
Assuming you want this based on the numeric part only you have to replace everything that is not a number, for which you can use REGEXP_REPLACE()
select regexp_replace(escalation_level, '[^[:digit:]]')
from my_table
To obtain the Nth value for a given CONFIG_ID it would be:
select nth_value(escalation_level, n)
over ( partition by config_id
order by regexp_replace(escalation_level, '[^[:digit:]]') )
from my_table
where n
is the index of the value you want to return.
Upvotes: 1
Reputation: 7137
Just a hint, you should use
RANK() OVER (PARTITION BY Config_ID ORDER BY Escalation_Level)
in this way you will be able to make Oracle to give you the Nth position of each Escalation_Level. When you should apply a filter.
Anyway check the RANK and the DENSE_RANK analytic functions in the Oracle documentation
Upvotes: 0