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

Question

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.

@DaleK sorry. I am still new to SO. I thought this would give the best view to other users to look into the problem. — Ryukojin, Aug 12 '20 at 10:48
This is related; https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — John Mitchell, Aug 12 '20 at 11:51

score 3 · Accepted Answer · answered Aug 12 '20 at 10:13

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

Thanks a lot @JMabee . Using `OVER()` and `ROW_NUMBER()` combo solved it. — Ryukojin, Aug 12 '20 at 11:38
What if a Boston has a count of 12 for both Latin and Metal? Only one will be included right? — John Mitchell, Aug 12 '20 at 11:50
@John Mitchell: Yep. If you want both, replace `ROW_NUMBER` with `RANK` or `DENSE_RANK`. — Thorsten Kettner, Aug 12 '20 at 11:55

John Mitchell · Answer 2 · 2020-08-12T11:46:21.900

This use of MAX should work.

Edit; Added inner join. Thanks to Gordon Linoff for the observation that my original answer didn't actually achieve anything.

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)
SELECT a.City, a.Genre, a.counts
FROM q_table a
INNER JOIN (
    SELECT City, MAX(counts) counts
    FROM q_table
    GROUP BY City
) b ON a.City = b.City AND a.counts = b.counts;

Thanks for pointing it out, I've edited accordingly. – John Mitchell Aug 12 '20 at 11:48 — John Mitchell, Aug 12 '20 at 11:48

score -1 · Answer 3 · answered Aug 12 '20 at 11:29

-1

try this

 with q_table
 as 
 (select * from (
 (   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)) as t3 where count in (select max(count) count from t3 group by city)

answered Aug 12 '20 at 11:29

chris

1
2

1

Firstly, your "count" should be "counts". Secondly, this will not return distinct city,genre combinations. If in the example, Cupertino also had a value of 12 counts; then that would also show because 12 is the max count for a different city. – John Mitchell Aug 12 '20 at 11:35
@chris I agree with John. But thanks for trying to help. – Ryukojin Aug 12 '20 at 11:40

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

3 Answers3