Question: 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](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d5f1a11b024e3ad4af706cda9f84a8cb).  

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

```
For the above question, following query will work: (Assume table name - data)

```sql
select Name , valuta , sum(price) as price , Type, Type2 
from data 
group by 1,2,4,5 
order by sum(price) desc ; 

```