Select two non unique columns by the highest value from the third-CodePudding

I have the next table:

CountryName                           BrandName                                Freq

Christmas Island                      Apple                                    1
Greece                                Apple                                    1
China                                 LG                                       3
China                                 Samsung                                  2
United States                         Ryzen                                    1
United States                         Samsung                                  3
United States                         Apple                                    3

How can I select country with the most frequent brands name and got result like this:

CountryName                                                     MostCommBrand

China                                                           LG
Christmas Island                                                Apple
Greece                                                          Apple
United States                                                   Apple
United States                                                   Samsung

CodePudding user response：

I suggest you use RANK. Now would also be a good time to explore the other ranking functions as well since they are so useful.

with cte as (
   select *, rank () over (partition by countryname order by freq desc) as rnk
   from @table)
select * from cte 
where rnk = 1 
order by countryname, brandname 
;

See fiddle for example. You can comment out the WHERE clause to see what value is generated by the RANK function for all rows to help you understand how it works. Also notice the complete script provided in the fiddle - that is something YOU should do to help encourage others to help you.