sql - In Oracle: How to sort by groupcluster by a column value - Stack Overflow

admin管理员组

文章数量:1023744

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

• sql
• oracle-database

Share Improve this question Follow edited Nov 20, 2024 at 1:30 Christian Bongiorno asked Nov 18, 2024 at 18:29 Christian BongiornoChristian Bongiorno 5,69244 gold badges4545 silver badges9898 bronze badges 2

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

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.

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

• sql
• oracle-database

Share Improve this question Follow edited Nov 20, 2024 at 1:30 Christian Bongiorno asked Nov 18, 2024 at 18:29 Christian BongiornoChristian Bongiorno 5,69244 gold badges4545 silver badges9898 bronze badges 2

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

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.

本文标签： sqlIn Oracle How to sort by groupcluster by a column valueStack Overflow