Here is a sample of my data

select * from (
    select 'A' as POD, 164 as result from dual union all
    select 'A' as POD, 3 as result from dual union all
    select 'A' as POD, 2 as result from dual union all
    select 'B' as POD, 409 as result from dual union all
    select 'B' as POD, 128 as result from dual union all
    select 'B' as POD, 5 as result from dual union all
    select 'C' as POD, 12391 as result from dual union all
    select 'C' as POD, 624 as result from dual union all
    select 'C' as POD, 405 as result from dual union all
    select 'C' as POD, 26 as result from dual union all
    select 'C' as POD, 3 as result from dual union all
    select 'C' as POD, 2 as result from dual
)

Here is a sample of my data

select * from (
    select 'A' as POD, 164 as result from dual union all
    select 'A' as POD, 3 as result from dual union all
    select 'A' as POD, 2 as result from dual union all
    select 'B' as POD, 409 as result from dual union all
    select 'B' as POD, 128 as result from dual union all
    select 'B' as POD, 5 as result from dual union all
    select 'C' as POD, 12391 as result from dual union all
    select 'C' as POD, 624 as result from dual union all
    select 'C' as POD, 405 as result from dual union all
    select 'C' as POD, 26 as result from dual union all
    select 'C' as POD, 3 as result from dual union all
    select 'C' as POD, 2 as result from dual
)

And what I want is for them to be sorted by group with highest first count:

I am not even sure how to express this as SQL

C has the highest result in the first row, then B has the next highest then A

• sql
• oracle-database

• Please explain precisely what leads to showing C before B before A. – Thorsten Kettner Commented Nov 19, 2024 at 8:30
• updated for clarity – Christian Bongiorno Commented Nov 20, 2024 at 1:31

2 Answers 2

I understand the task as follows:

1. Look at the pods' results. Show the pod with the highest result first, then the pod with the next highest, etc.
2. Order the rows in each pod by result descending.

Now, what do we consider the highest result? Is this the maximum result per pod? That would be A = 164, B = 409, C = 12391. We would get this with

MAX(result) OVER (PARTITION BY pod)

Or is it the maximum result sum per pod? That would be A = 169, B = 542, C = 13446. And the expression we'd need is

SUM(result) OVER (PARTITION BY pod)

Use one or the other in your ORDER BY clause. E.g.:

SELECT pod, result
FROM mytable
ORDER BY
  MAX(result) OVER (PARTITION BY pod) DESC,
  pod,
  result DESC;

another way is by using the row_number in this way to sort your data desc:

select * from (
    select 'A' as POD, 164 as result from dual union all
    select 'A' as POD, 3 as result from dual union all
    select 'A' as POD, 2 as result from dual union all
    select 'B' as POD, 409 as result from dual union all
    select 'B' as POD, 128 as result from dual union all
    select 'B' as POD, 5 as result from dual union all
    select 'C' as POD, 12391 as result from dual union all
    select 'C' as POD, 624 as result from dual union all
    select 'C' as POD, 405 as result from dual union all
    select 'C' as POD, 26 as result from dual union all
    select 'C' as POD, 3 as result from dual union all
    select 'C' as POD, 2 as result from dual
)
order by last_value(result) OVER (PARTITION BY pod order by result rows between unbounded preceding and unbounded following) desc, result desc

Hope it helps.