Grouping with order in complex data

Tags: ,



I have data like this

Name        valuta         price        Type    Type2
A            USD            10          Acc       1
B            USD            30          Acc       2
C            SGD            20          Acc       3
D            SGD            05          Acc       4
A            SGD            35          Acc       1
C            SGD            05          Acc       3
B            USD            50          Rej       2

Grouping based on Name, valuta and type. (type2 always have a unique value based on name) and sum the price when have the same group condition, the last step is to order based the highest price followed by same group condition.

And after process (Group & Order), the output should be like this:

Name        valuta         price        Type    Type2
B            USD            50          Rej       2
B            USD            30          Acc       2
A            SGD            35          Acc       1
A            USD            10          Acc       1
C            SGD            25          Acc       3
D            SGD            05          Acc       4

I tried use cte because pipeline process design seems clear to me but the result is wrong.

WITH Cte_Process1 AS
(
   SELECT
      Name,
      valuta,
      SUM(price) AS price,
      Type,
      Type2
   FROM table1
   GROUP BY 
      Name,
      valuta,
      price,
      Type,
      Type2
)
SELECT * FROM Cte_Process1 ORDER BY price

Answer

You can do it with MAX() window function in the ORDER BY clause:

select Name, valuta, sum(price) price, type, type2
from tablename
group by Name, valuta, type, type2
order by max(sum(price)) over (partition by Name) desc, 
         Name,
         sum(price) desc

See the demo.
Results:

> Name | valuta | price | type | type2
> :--- | :----- | ----: | :--- | ----:
> B    | USD    |    50 | Rej  |     2
> B    | USD    |    30 | Acc  |     2
> A    | SGD    |    35 | Acc  |     1
> A    | USD    |    10 | Acc  |     1
> C    | SGD    |    25 | Acc  |     3
> D    | SGD    |     5 | Acc  |     4


Source: stackoverflow