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
)
POD | RESULT |
---|---|
A | 164 |
A | 3 |
A | 2 |
B | 409 |
B | 128 |
B | 5 |
C | 12391 |
C | 624 |
C | 405 |
C | 26 |
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
)
POD | RESULT |
---|---|
A | 164 |
A | 3 |
A | 2 |
B | 409 |
B | 128 |
B | 5 |
C | 12391 |
C | 624 |
C | 405 |
C | 26 |
And what I want is for them to be sorted by group with highest first count:
POD | RESULT |
---|---|
C | 12391 |
C | 624 |
C | 405 |
C | 26 |
B | 409 |
B | 128 |
B | 5 |
A | 164 |
A | 3 |
A | 2 |
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
I understand the task as follows:
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.