Alasql - group by and take the maximum result

Question

I have a table in the below format:

Vendor Id  Weight
 AAA   1   1234
 AAA   1   121
 AAA   2   5182
 BBB   1   311 
 BBB   1   9132
 BBB   2   108

I need to group by "Vendor" and "Id" and sum the "Weight". Whichever "Id" has the maximum "Weight" has to be assigned to the corresponding "Vendor". In the below example, Id "2" of Vendor "AAA" has a maximum weight of 5182 compared to the Id "1" whose weight is 1355 (1234+121). Hence Id "2" should be assigned to vendor "AAA". Similarly for Vendor "BBB", Id "1" has to be assigned as it has the maximum weight 9443 (311+9132) compared to Id "2" whose weight is "108".

Result has to be

Vendor Id
AAA    2
BBB    1

I am trying to implement this in "Alasql" which is the query language for Google Apps Script.

Any suggestions would be appreciated.

eshirvana · Answer 1 · 2021-04-05T18:48:28.007

0

you can use window functions to do that:

select * from (  
   select Vendor,id,sum(weight) summWeight, row_number() over (order by sum(weight) desc) rn
   from yourtable
   group by Vendor,id
) tt
where rn = 1

then this is how you can do it:

select t.*
from (
   select Vendor,max(summWeight) Maxweight from (  
      select Vendor,id,sum(weight) summWeight
      from yourtable
      group by Vendor,id
    ) tt
   group by Vendor
) tt join (
      select Vendor,id,sum(weight) summWeight
      from yourtable
      group by Vendor,id
) t
on t.vendor = tt.vendor
and summWeight = Maxweight

edited Apr 05 '21 at 18:48

answered Apr 05 '21 at 18:08

eshirvana

23,227
3
22
38

Thank you for the answer. Unfortunately, there is no "over" and "order by" in alasql library – user3447653 Apr 05 '21 at 18:21
Since there are no "row_number" in alasql, can this be implemented without using any functions? – user3447653 Apr 05 '21 at 18:38
@user3447653 see the second query , hopefully that works – eshirvana Apr 05 '21 at 18:48

Alasql - group by and take the maximum result

1 Answers1