Re: How do i get the max row from a calculated query?

[Nick Abelson]

thanks for helping me but still can't get it to work!
This is what I am doing as I want to get the name and amount of the drink that has been drunk the most:
Select MAX
(totalunits, drinktype.drinkname)
FROM
(SELECT
drinktype.drinkname,
drinkusage.amount,
drinktype.units,
SUM(
DISTINCT drinkusage.amount * drinktype.units
) AS totalunits,
drinkusage.dateofdrink
FROM
drinkusage
INNER JOIN drinktype ON drinktype.drinkid = drinkusage.drinkid
WHERE
drinkusage.userid = '170'
GROUP BY
drinkusage.userid,
drinktype.drinkname,
drinkusage.amount,
drinktype.units,
drinkusage.dateofdrink)
ORDER BY
totalunits
DESC