-2

I am a bit stuck on how to get all the rows I want from the max count of one column, grouping by only some specific columns. For example, over here I want to group by user and name so that they are always distinct. But for country and device type I only want the combination of their max event_ct to show up

user name country device event_ct
5 Albert US Mac 5
5 Albert GB Phone 7
5 Albert CN Mac 3
6 Albert CN Mac 1
7 Laurel US Phone 7

I want a mysql query to turn it into

user name country device
5 Albert GB Phone
6 Albert CN Mac
7 Laurel US Phone

Please help! Thanks :-)

Shadow
  • 33,525
  • 10
  • 51
  • 64
Emily White
  • 1
  • 2
  • Does this answer your question? [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) – T. Peter Jan 08 '21 at 05:51
  • I think it is a bit different. He just needs a distinct column (home) with the max of another column (resource). I need to have mines grouped by 2 column (user, name) but the 2 column combination (country, device) has to be the max of the event_ct. – Emily White Jan 08 '21 at 05:55

1 Answers1

0

Although I still think this is a duplicate question but for the good measure.

you can use subquery in where clause:

select user,name,country,device from cte
where event_ct in (select max(event_ct) from cte group by user,name)

here is db<>fiddle for better examine.

group by and distinct are fundamental same in some way, because you are telling SQL that you want UNIQUE row. group by a,b will give you distinct combination of a and b since it "GROUP" all the same (a,b) combination together.

re-edit

in some case if you got more then one row with max event_ct
for example user 5 got two row with event_ct = 7
you can add distinct to get rid of duplicate.

T. Peter
  • 887
  • 4
  • 13