Oracle - Sorting Conditionally on String and Number

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.

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

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)