Vicyorus Vicyorus - 6 months ago 7
MySQL Question

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

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
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.

Comments