CODEWITHSUNDEEP
oracle-databasesortingdynamic
Landon Statis
Landon Statis

Reputation: 839

Oracle - Sorting Conditionally on String and Number

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.......

Upvotes: 2

Views: 263

Answers (3)

Darrel Lee
Darrel Lee

Reputation: 2460

Have you looked at using the DECODE function?

Decode is kind of like a simplified CASE statement. You can use it in the order by clause (or anywhere you can use an expression)

DECODE(expr, key1, value1, key2, value2, default)

For example:

ORDER BY
DECODE(tbl.companyName, 'name1', 1, 'name2', 2, 'name3', 3, 9999)

The last value is the default if the first expression doesn't match.

In your example, I think you can combine CASE and DECODE to yield the final expression for the order by clause.

Anther trick for sorting numbers as characters is to pad the string with leading zeros. You can use LPAD or concatenation and substr (RIGHT)

    LPAD(expr, 9, '0');
    SUBRSTR('000000000' || expr, -9);

I use something like the above to fix SSNs that have had leading 0 dropped (for example when they come through Excel)

Upvotes: 0

Boneist
Boneist

Reputation: 23578

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.

Upvotes: 6

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

What if you take your case outside of window function -

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

Upvotes: 4

Related Questions