Need help sorting table by parentid, childid and sum of amount by parent

user_2836088 0
Viewed 29

I wanted to sort table by parentid, childid and sum of amount by parent

Given sample data:

parentid    childid     amount
FGH         P1          12
FGH         P2          323
ABC         BB          123
ABC         XX          563
ABC         ZZ          781
SSS         J8          4200

first step: sort parentids by sum of amount

ABC has sum of amount = 1467, FGH has sum of amount = 335, SSS has sum of amount = 4200 and gonna use this amount sums to sort by parentid.

second step: sort childid by individual amount

final desired sorted table

parentid    childid     amount
SSS         J8          4200
ABC         ZZ          781
ABC         XX          563
ABC         BB          123
FGH         P2          323
FGH         P1          12

I solved this by chaining a bunch of common table expressions to generate a row_number() twice: first is for sum(amount) grouping by parentid - named sort1 second is for amount, partitioned by parentid - named sort2 then the final column "rowIndex" is : sort1 * 15 + sort2. 15 is my magic number for the max number of child for each parent.

my main problem is, the query takes 300-50 ms to execute for only 600 rows. I created a view out of this query so I can join it and use the rowIndex to sort my final table. works but too slow for my liking.

Whats the best way to do it? I'm using t-sql/sql server.

sql
1 Answers

This is one solution. Replace the CTE with a ORDER BY SUM() PARTITIONED BY ParentID. I added a second order on ParentID for ties.

SELECT
    ParentID,
    ChildID,
    Amount
FROM
    MyTable
ORDER BY
    SUM(Amount) OVER(PARTITION BY ParentID) DESC,
    ParentID,
    Amount DESC
user_6115450
Related