Re: SQL for aggregate group by

this should work ,.. let me know

select * from tab_x
where qty = (select max(qty) from tab_x)

bye

"Robert Eden" <rmeden_at_yahoo.com> wrote in message news:Lzmpb.2237$lv4.1354_at_newssvr23.news.prodigy.com...
> 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 Wed Nov 05 2003 - 20:24:58 CST