SQL Query Question, Find max of result
I am trying create a query that finds a single row with the highest value. I am working in a Oracle 10G environment right n开发者_如何学编程ow.
I created this query first which lists the quantities
SELECT aname, sum(rhowmuch * rhowoften) AS Quantity
FROM ration
GROUP BY ration.aname;
ANAME QUANTITY
---------- ----------
BLACKIE 16
CONRAD 29.4
MEO 28
MOLLY 37.7
CHUBBLES 75
BOSS 63.7
JEFFREY 29.4
FANNY 56
SUZY 21
ZEO 17.5
MORRIS 21
ANAME QUANTITY
---------- ----------
SALLY 21
LEO 40
SAM 28
14 rows selected.
But I can't figure out a way to create a single query that pulls the maximum quantity from there. Any Suggestions?
Use:
SELECT x.aname, x.quantity
FROM (SELECT r.name, SUM(r.rhowmuch + r.rhowoften) AS quantity
FROM RATION r
GROUP BY r.aname
ORDER BY quantity DESC) x
WHERE ROWNUM = 1
For more info on using ROWNUM, see: http://blog.lishman.com/2008/03/rownum.html
SELECT aname, sum(rhowmuch * rhowoften) AS Quantity
FROM ration
GROUP BY ration.aname
order by Quantity desc
limit 1
精彩评论