How to choose top groups then ungroup

Question

I have this table

+-------+----------+------+
| Group | Function | Name |
+-------+----------+------+
| G1    | F1       | ABC  |
| G1    | F1       | ABC  |
| G1    | F2       | ABC  |
| G1    | F3       | ABC  |
| G2    | F1       | XYZ  |
| G2    | F2       | XYZ  |
| G2    | F3       | XYZ  |
| G2    | F4       | XYZ  |
| G3    | F1       | LMN  |
| G3    | F2       | LMN  |
| G3    | F2       | LMN  |
| G3    | F2       | LMN  |
| G4    | F1       | QRX  |
| G4    | F2       | QRX  |
| G4    | F3       | QRX  |
| G4    | F4       | QRX  |
| G4    | F5       | QRX  |
+-------+----------+------+

so

G1 have 3 Distinct functions
G2 have 4 Distinct functions
G3 have 2 Distinct functions
G4 have 5 Distinct functions

I want to take top 2 Groups that have highest number of distinct functions which are G4 and G2, then ungroup them to have the output like below :

+-------+----------+------+
| Group | Function | Name |
+-------+----------+------+
| G2    | F1       | XYZ  |
| G2    | F2       | XYZ  |
| G2    | F3       | XYZ  |
| G2    | F4       | XYZ  |
| G4    | F1       | QRX  |
| G4    | F2       | QRX  |
| G4    | F3       | QRX  |
| G4    | F4       | QRX  |
| G4    | F5       | QRX  |
+-------+----------+------+

attempt :

select * 
from mytable
where [Group] in
 (
   SELECT [Group]
   FROM mytable
   group by [Group]

 )

score 2 · Accepted Answer · answered Apr 15 '18 at 12:10

Your attempt was along the right lines.

You can use

SELECT *
FROM   mytable
WHERE  [Group] IN (SELECT TOP 2 WITH TIES [Group]
                   FROM   mytable
                   GROUP  BY [Group]
                   ORDER  BY COUNT(DISTINCT [Function]) DESC)

The TOP 2 WITH TIES means that you may end up with more than 2 groups if multiple groups are tied with the same distinct function count.

Remove the WITH TIES if you don't want that and just want 2 with arbitrary tied ones dropped.

How to choose top groups then ungroup

1 Answers1