Return of Maximum value

Asked

Viewed 62 times

1

Good morning, I’m conducting a course and I’m stuck on an exercise.

In the exercise I have to return the maximum popularity value of each musical genre by country, so far all well, I managed to perform the procedure, the problem is that in the exercise has country that the maximum popularity value repeats, that is, has country that can have two musical generos of equal popularity.

I would like to know how to display the two generous, given that when I put GROUP BY it groups everything and displays only one country, and if there is another way to do this without sub-query

SELECT MAX(total) total, pais, genero, id_genero
FROM(SELECT Count(*) total, BillingCountry pais, g.Name genero, g.GenreId id_genero
        FROM Genre g
        JOIN Track t
        ON g.GenreId = t.GenreId
        JOIN InvoiceLine il
        ON t.TrackId = il.TrackId
        JOIN Invoice i
        ON il.InvoiceId = i.InvoiceId
        GROUP BY 2, 3
        ORDER BY 2, 1 DESC)t1
GROUP BY 2

Modelo de Dados

Resultado a ser obtido

sql sqlite

  • I could ask the question your data model and if possible a 2 lines with data to understand better?

  • In thin, you are setando a group by 2, technically, depending on the case, you will always have the return of a column even, you took a look at that? if it’s something from the sgbd syntax you’re using, ignore the comment then ...

  • Ricardo, I put the data model and the result I have to get

  • Tip , use a subselect with MAX ( https://forum.imasters.com.br/topic/512214-selected-item-max/? do=findComment&comment=2030107 )

  • Motta, Voce tells me to make another query in select that already has MAX?

  • Motta, got it, thanks for your help

Show 1 more comment

1 answer

0

Good night,

With this code below, if there are generous with equal values he will bring both.

OBS:I left the code as much space as possible for better understanding.

SELECT T1.* FROM (SELECT C.Country, G.name, COUNT(*) QTD FROM Genre G JOIN Track T ON G.Genreid = T.Genreid JOIN Invoiceline IL ON T.Trackid = IL.Trackid JOIN Invoice I ON IL.Invoiceid = i.Invoiceid JOIN Customer C ON I.Customerid = C.Customerid GROUP BY 1,2) T1

JOIN

(SELECT Country,Max(QTD) QTD FROM (SELECT C.Country, G.name, COUNT(*) QTD FROM Genre G JOIN Track T ON G.Genreid = T.Genreid JOIN Invoiceline IL ON T.Trackid = IL.Trackid JOIN Invoice I ON IL.Invoiceid = i.Invoiceid JOIN Customer C ON I.Customerid = C.Customerid GROUP BY 1,2) GROUP BY 1) T2 ON T1.Country = T2.Country AND T1.QTD = T2.QTD

Browser other questions tagged sql sqlite

You are not signed in. Login or sign up in order to post.