CODEWITHSUNDEEP
oracle-database
Sriram B
Sriram B

Reputation: 691

Finding Nth Minimum of a Varchar value in Oracle

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

Answers (2)

Ben
Ben

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

mucio
mucio

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

Related Questions