Re: Simple Top N question

Yup! Thomas is right. I made the second query first and was a bit too adventurous in making the first one :-) sorry about posting w/o testing and oversight....

BTW, Thomas, which news reader do you use ? ( I use Outlook Express and it sucks! )

--
V.Satheesh Babu http://csoft.net/~vsbabu vsbabu_at_csoft.net
vsbabu_at_erols.com
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:LLspOPxXjuXsKkCcyeU01EFsAAFr_at_4ax.com...
> A copy of this was sent to "Babu" <vsbabu_at_erols.com>
> (if that email address didn't require changing)
> On Wed, 10 Nov 1999 11:45:15 -0500, you wrote:
>
> >SELECT
> > A.customer
> > , MAX(A.order_num) order_num
> > , MAX(A.quantity) quantity
> >FROM
> > orders A
> >GROUP BY
> > A.customer
> >/
> >
> >should work. Or more elaborately (and slowly)
> >
>
> no -- that will get SOME max order_num and SOME max quantity -- the order
num
> and quantity will probably *NOT* be from the same rows (you'll get an
order_num
> from one row -- the one with the max order_num and a quantity from some
other
> row, they are no correlated)
>
> >SELECT
> > A.customer
> > , MAX(A.order_num) order_num
> > , A.quantity
> >FROM
> > orders A
> >WHERE
> > A.quantity >= (
> > SELECT
> > MAX(B.quantity)
> > FROM
> > orders B
> > WHERE
> > B.customer = A.customer
> > )
> >GROUP BY
> > A.customer
> > , A.quantity
> >/
>
> That is one that would work...
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Wed Nov 10 1999 - 12:53:06 CST