SQL for aggregate group by

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 Received on Mon Nov 03 2003 - 00:27:55 CST