开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜