How to do I query all distinct rows with only their highest values?

I have been trying to query each city’s popular genre. I am only trying to get the rows that I have highlighted. I tried using MAX() on a group by but gave me a syntax error.

My CTE query is as follows, its based on the dbeaver sample dataset:

with q_table
as 
(   select City, Genre, count(*) as counts
    from 
        (select c.City, g.Name as Genre
        from bus5dwr.dbeaver_sample.Customer c
        inner join bus5dwr.dbeaver_sample.Invoice i
            on i.CustomerId = c.CustomerId
        inner join bus5dwr.dbeaver_sample.InvoiceLine il
            on il.InvoiceId = i.InvoiceId 
        inner join bus5dwr.dbeaver_sample.track t
            on t.TrackId = il.TrackId 
        inner join bus5dwr.dbeaver_sample.Genre g
            on g.GenreId = t.GenreId 
        where Country = 'USA'
        ) as t2
    group by City, Genre)

I tried the following query.

Answer

I don’t have a dataset to test this on, but you should be able to just add a ROW_NUMBER() function to your CTE to get the values you are looking for. Such as:

with q_table
as 
(   select City, Genre, count(*) as counts,
    ,ROW_NUMBER() OVER(partition by City order by count(*) desc) RN
    from 
        (select c.City, g.Name as Genre
        from bus5dwr.dbeaver_sample.Customer c
        inner join bus5dwr.dbeaver_sample.Invoice i
            on i.CustomerId = c.CustomerId
        inner join bus5dwr.dbeaver_sample.InvoiceLine il
            on il.InvoiceId = i.InvoiceId 
        inner join bus5dwr.dbeaver_sample.track t
            on t.TrackId = il.TrackId 
        inner join bus5dwr.dbeaver_sample.Genre g
            on g.GenreId = t.GenreId 
        where Country = 'USA'
        ) as t2
    group by City, Genre)

SELECT City, Genre, Counts 
from q_table
WHERE RN=1
Order BY City