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
Advertisement
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