how to get most frequent value when grouped by ID?

Asked Jul 08 '19 at 16:32

Active Jul 08 '19 at 17:32

Viewed 29 times

Need to find most occuring value when grouped by ID

EDIT : I grouped the names with ID, using this ID, it has a person name associated with it, each name has multiple cities, I need to pull a unique name with the most frequent city. Min and Max functions do not work I think.

I hope below table example is helpful.

Col_1  Col_2  
1        A       
1        B       
1        A       
2        D 
2        D
2        C

I want result as

Col_1  Col_2  
1        A       
2        D

edited Jul 08 '19 at 17:32

asked Jul 08 '19 at 16:32

Dwight K

_when grouped by ID_ So which column that you actually show us is the ID column in question – RiggsFolly Jul 08 '19 at 16:40
@nacho that doesn't show the most common value in each group, it shows the most common overall. – Barmar Jul 08 '19 at 16:43
What version of MySQL? – Shawn Jul 08 '19 at 16:44
Sorry, it is AWS Athena. – Dwight K Jul 08 '19 at 17:28
EDIT : I grouped the names with ID, using this each name has multiple cities, I need to pull a unique name with the most frequent city. Min and Max functions do not work I think. – Dwight K Jul 08 '19 at 17:30

how to get most frequent value when grouped by ID?

0 Answers0