How to select the entire tuple that has the MAX value from a subquery?

Question

Before someone asks, yes, this is part of a homework, however, I have tried and searched for similar solutions yet none have worked.

Also important to be noticed is that this should run on a MySQL server, so any special syntax from them should be considered.

For this, you will need four tables:

person (idperson INT, firstName VARCHAR(45))
beer (idbeer INT, name VARCHAR(45))
purchase(idpurchase INT, idperson INT)
beerxpurchase(idpurchase INT, idbeer INT, quantity INT)

Where each of the first columns is the primary key except on beerxpurchase, which is a composed key from the first and second column, and all of the other columns that share names represent a foreign key.

In the end, I need to make a query that returns each individual person with the id of the beer they have bought the most in all of their purchases, and the total amount of that kind of beer that they have bought.

A query such as the following:

SELECT person.firstName NAME, beerxpurchase.idBeer BEER, SUM(beerxpurchase.quantity) TOTAL
FROM person

INNER JOIN purchase
on person.idperson = purchase.idperson

INNER JOIN beerxpurchase
ON beerxpurchase.idpurchase = purchase.idpurchase

GROUP BY person.firstName, beerxpurchase.idbeer;

Will return the total amount of beers purchased for each specific beer code per person, something like this:

NAME   BEER    TOTAL
A      1       5
A      2       23
A      3       3
A      4       19
A      5       7
B      1       11
B      2       3
B      3       3
B      4       4
B      5       5
C      3       5
D      1       8
D      2       4
D      4       1
D      5       10
E      1       9
E      4       10

That is what I consider a good start, nonetheless, from that query I should only get the tuple that has the maximum quantity per user. Sounds like a GROUP BY NAME, however, running something like:

SELECT preferenence.FIRST_NAME, preferenence.ID_BEER, MAX(preferenence.TOTAL_BOUGHT) FROM
(
    SELECT person.firstName FIRST_NAME, beerxpurchase.idBeer ID_BEER, SUM(beerxpurchase.quantity) TOTAL_BOUGHT
    FROM person

    INNER JOIN purchase
    on person.idperson = purchase.idperson

    INNER JOIN beerxpurchase
    ON beerxpurchase.idpurchase = purchase.idpurchase

    GROUP BY person.firstName, beerxpurchase.idbeer
) preferenence

GROUP BY preferenence.FIRST_NAME
ORDER BY MAX(preferenence.TOTAL_BOUGHT) DESC;

Will lose the correct beer ID, but will keep the correct person name and total amount of beers bought, something like this:

FIRST_NAME     ID_BEER      TOTAL_BOUGHT
A              1            23
B              1            11
D              1            10
E              1            10
C              3            5

From what I have read, most DBMSs won't keep track of ID_BEER because it's not part of the GROUP (as far as I understood). However, changing the last GROUP to GROUP BY preference.FIRST_NAME, preference.ID_BEER will return the same tuples from the internal subquery.

So the question is: from that subquery, how would one go on to get the entire tuple associated to that specific maximum value? Or in case you can propose a better solution to the subquery or the query in general, it's more than welcome.

Anything that's not entirely clear or if you need more information about the tables and their relationships, please let me know.

Answer 1

SELECT p.firstName
     , b.name beer
     , m.total
  FROM 
     ( SELECT o.idperson
            , bo.idbeer
            , SUM(bo.quantity) total
         FROM beerxpurchase bo 
         JOIN purchase o 
           ON o.idpurchase = bo.idpurchase 
        GROUP
           BY o.idperson
            , bo.idbeer
     ) m
  JOIN 
     ( SELECT idperson
            , MAX(total) total
         FROM 
            ( SELECT o.idperson
                   , bo.idbeer
                   , SUM(bo.quantity) total
                FROM beerxpurchase bo 
                JOIN purchase o 
                  ON o.idpurchase = bo.idpurchase 
               GROUP
                  BY o.idperson
                   , bo.idbeer
            ) x
        GROUP
           BY idperson
     ) n
    ON n.idperson = m.idperson
   AND n.total = m.total
  JOIN person p
    ON p.idperson = m.idperson
  JOIN beer b
    ON b.idbeer = m.idbeer

http://sqlfiddle.com/#!9/c6bb9/15

I know you didn't ask for the name of the beer, but personally I tend to order beer by name rather than id.

Answer 2

To get the beer_id which is the most purchased beer by a person, you need to follow this process:-

1. Find total number of beer of each category purchased by a person (which is already done)
2. Find maximum number of beer from those category by a person (only count not beer_id)
3. Join 1's & 2's result with count & person

Note: Here, I group by with idperson because different person can have same firstName.

Here is full query:-

SELECT person.firstname, personBeer.idbeer, personPurchase.TOTAL_BOUGHT
FROM
(
   SELECT purchaseInfo.idperson, MAX(purchaseInfo.TOTAL_BOUGHT) TOTAL_BOUGHT
   FROM
   (
      SELECT purchase.idperson, beerxpurchase.idbeer, 
           SUM(beerxpurchase.quantity) TOTAL_BOUGHT
      FROM purchase
      INNER JOIN beerxpurchase ON beerxpurchase.idpurchase = purchase.idpurchase
      GROUP by purchase.idperson, beerxpurchase.idbeer
   ) purchaseInfo
   GROUP BY purchaseInfo.idperson
) personPurchase
INNER JOIN person ON person.idperson = personPurchase.idperson
INNER JOIN 
(
    SELECT purchase.idperson, beerxpurchase.idbeer, 
        SUM(beerxpurchase.quantity) TOTAL_BOUGHT
    FROM purchase
    INNER JOIN beerxpurchase ON beerxpurchase.idpurchase = purchase.idpurchase
    GROUP by purchase.idperson, beerxpurchase.idbeer  
) personBeer 
ON personBeer.TOTAL_BOUGHT  = personPurchase.TOTAL_BOUGHT 
    AND personBeer.idperson = person.idperson
ORDER BY personPurchase.TOTAL_BOUGHT DESC

Hope It helps you.