CODEWITHSUNDEEP
google-sheetsgoogle-query-language
Per-Åke Franklind
Per-Åke Franklind

Reputation: 17

Select top n unique filtered rows in Google Sheets

In Google Sheets I have a table like this with over 10 000 rows:

A          B        C           D
Mewtwo     33,5     Confusion   Shadow Ball
Mewtwo     33,5     Confusion   Hyper Beam
Alakazam   38       Confusion   Futuresight
Dragonite  38,5     Dragon Tail Outrage
Mewtwo     33,5     Confusion   Focus Blast
Mewtwo     33,5     Psycho Cut  Shadow Ball
Rhydon     37,5     Mud Slap    Earthquake
Mewtwo     33,5     Psycho Cut  Hyper Beam
Mewtwo     33,5     Confusion   Psychic
Mewtwo     33,5     Psycho Cut  Focus Blast
Gengar     29       Shadow Claw Shadow Ball
Dragonite  38,5     Dragon Tail Dragon Claw
Dragonite  38,5     Dragon Tail Hyper Beam
Alakazam   38       Confusion   Shadow Ball
Mewtwo     28       Confusion   Shadow Ball
Gengar     33,5     Hex         Shadow Ball
Mewtwo     33,5     Psycho Cut  Psychic
Mewtwo     28       Confusion   Hyper Beam

What I want is the first unique row of each combination of A, C and D with the highest number in column B, the table is already sorted by B. So for the table above I need a filter to exclude following rows:

Mewtwo     28       Confusion   Shadow Ball
Mewtwo     28       Confusion   Hyper Beam

And then add: ARRAY_CONSTRAIN(...;30;4) to get top 30

Upvotes: 1

Views: 967

Answers (1)

TheMaster
TheMaster

Reputation: 50443

=QUERY(A2:D10000,"select A, max(B),C, D group by A, C, D limit 30")

Upvotes: 1

Related Questions