Return of the maximum value

Question

Return of the maximum value

Navigation

#1 by (0 votes)

1

Good morning, I'm taking a course and I'm stuck in an exercise.

In the exercise I have to return the maximum value of popularity of each musical genre by country, so far so good, I managed to carry out the procedure, the problem is that in the exercise country has the maximum popularity value repeats, has a country that can have two musical genres of equal popularity.

I would like to know how to display the two genres, since when I put GROUP BY it groups everything and only displays one country, and if you have another way to do it without subquery

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

sql sqlite

asked by anonymous 29.06.2018 / 14:18

1 answer

if and else condition with date in crystal report How to send SQL data with pre-defined data in PHP

score 0 · Answer 1

Good evening,

With this code below, if there are generals with equal values it will bring both.

NOTE: 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