Re: SQL for aggregate group by

Robert Eden wrote:

>I've been reading over the manuals for days, I know I'm close but can't
>figure it out!
>
>Let's say we have a table like this.
>
>ITEM ,TIME, QTY
>A,1,10
>A,2,20
>A,3,5
>A,4,6
>A,5,2
>
>I want to know the max count and the time it occurred (any time if more than
>one)
>
>select item,max(qty),
>??????
>from table_name group by item;
>
>The real table is more complicated, so I'm looking for efficiency here. The
>group by clause has already fetched the rows so I shouldn't need to use a
>self-join.
>
>My head is spinning trying to figure out "first", "over", and "first_value"
>clauses... this should be so simple! HELP!
>
>Robert
>
>

What you are trying to do will not work. Since this seems likely to be school
work I'll give you hints.

1. First thing you want to know is the MAX quantity:

SELECT MAX(...
FROM ... Then you want to use that in a subquery.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)