Multiple Max rows

Question

I need to write a query that determines the customer that has spent the most on music for each country. Write a query that returns the country along with the top customer and how much they spent. For countries where the top amount spent is shared, provide all customers who spent this amount.

You should only need to use the Customer and Invoice tables.

Check Your Solution

Though there are only 24 countries, your query should return 25 rows because the United Kingdom has 2 customers that share the maximum.

I build the query but There's one group having two maximum result I have to show

SELECT CustomerId , FirstName, LastName, Country, MAX(TotalSpent) AS  TotalSpent 
from  (select c.CustomerId as CustomerId, c.Firstname As FirstName,  c.LastName as LastName, i.BillingCountry as Country, SUM(i.Total) as TotalSpent
from customer c join invoice i
on c.CustomerId = i.CustomerId
   group by 1,2,3,4
   order by 5 desc
   limit by 1 ) AS temp
   group by 4

the expected outcome must be 25 rows not 24 United Kingdom have two customer share the same amount of maximum spent amount

Which RDBMS are you using? Also, don't sort/group by ordinal values like that. — dfundako, Jun 21 '19 at 15:27
This is a [tag:greatest-n-per-group] question, which comes up frequently on Stack Overflow. Follow that tag for many solutions. In SQL Server, you can use the window function `ROW_NUMBER()`. — Bill Karwin, Jun 21 '19 at 15:36
@BillKarwin Thank u but I need to do it with mutli select or join not with build in functions — Zaid Odeh, Jun 21 '19 at 15:38
That's an absurd requirement, but you can do it for example see my solution using left outer join here: https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql/1313293#1313293 That question is about MySQL, but the join solution would work in any brand of SQL database. — Bill Karwin, Jun 21 '19 at 15:42

mkRabbani · Answer 1 · 2019-06-21T16:54:01.817

This following script will help you getting your required results. I have used RANK in the script to pick One or More Customer with Maximum Amount sent per country.

    WITH Customer(CustomerId,FirstName,LastName)
AS
(
    SELECT 1, '1 FN', '1 LN' UNION ALL
    SELECT 2, '2 FN', '2 LN'
),
Invoice(CustomerId,Country,Total)
AS
(
    SELECT 1, 'C 1', 100 UNION ALL
    SELECT 1, 'C 1', 35 UNION ALL
    SELECT 2, 'C 1', 80 UNION ALL
    SELECT 2, 'C 1', 80 UNION ALL
    SELECT 1, 'C 2', 50 UNION ALL   
    SELECT 2, 'C 2', 100 UNION ALL
    SELECT 1, 'C 2', 50 UNION ALL
    SELECT 1, 'C 3', 17 UNION ALL   
    SELECT 2, 'C 3', 17
)

SELECT 
    B.Country, 
    B.CustomerId, 
    C.FirstName, 
    C.LastName, 
    B.T AS TotalSpent
FROM
(
    SELECT *, 
           RANK() OVER(PARTITION BY Country
           ORDER BY Country, 
                    T DESC) RN
    FROM
    (
        SELECT i.Country, 
               C.CustomerId, 
               SUM(i.Total) T
        FROM Customer C
             INNER JOIN Invoice I ON c.CustomerId = i.CustomerId
        GROUP BY i.Country, 
                 C.CustomerId
    ) A
) B
INNER JOIN Customer C ON B.CustomerId = C.CustomerId
WHERE B.RN = 1
ORDER BY 1,2

Output is(Considering the CTE Input)-

Country   CustomerId    FirstName   LastName    TotalSpent
C 1       2             2 FN        2 LN        160
C 2       1             1 FN        1 LN        100
C 2       2             2 FN        2 LN        100
C 3       1             1 FN        1 LN        17
C 3       2             2 FN        2 LN        17

Opps!! Where condition should be applied after JOINING. Edited. — mkRabbani, Jun 21 '19 at 16:29
:( I am not getting any issue for my SCRIPT. Added some CTE code so that you can check your self. — mkRabbani, Jun 21 '19 at 16:54

Multiple Max rows

1 Answers1