Oracle - Sorting Conditionally on String and Number

Question

I have a query where based on a column value, my sort will be dynamic. So, it is something like this:

ROW_NUMBER() OVER (PARTITION BY last_action 
                   ORDER BY CASE
                              WHEN last_action = 'Insert' THEN company_name
                              ELSE percent_change
                            END DESC

Issue here is that they are different data types, so it throws an error. If I convert the "percent_change" to character, then it does not sort numerically. And, to complicate things, they want the "percent_change" in DESC and the "company_name" in ASC.

So, I was thinking if there is a way to convert the actual "company_name" into some numerical value, and subtract it from 0, and then I can so the numerical sort in DESC order.

Any ideas would be helpful.......

Answer 1

Sounds like you're after something like this, then:

ROW_NUMBER() OVER (PARTITION BY last_action 
                   ORDER BY CASE
                              WHEN last_action = 'Insert' THEN company_name
                            END,
                            CASE
                              WHEN last_action = 'Insert' THEN NULL
                              ELSE percent_change
                            END DESC NULLS LAST)

This works by splitting the ordering out into two expressions, but because they're conditional on the column in the partition by clause, only one of them is going to impact the ordering at any one time.