Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with select
I can almost guarantee there is a way to do it in a single SQL statement.
I've seen a similar problem solved in an earlier thread on this list. At the
time I remember thinking, "That's pretty clever", but unfortunately I don't
remember how to do it. I remember it involves using some esoteric function
which counts the number of rows in an ordered group, and a final row is
printed if the count is 3, in this case. Anyone remember this one?
Paul Cluiss
Dallas, Texas
Alan Shein wrote:
> Sounds like a PL/SQL homework assignment calling for a cursor and nested
> IFs.
>
> <lily99_at_my-deja.com> wrote in message news:80um83$2km$1_at_nnrp1.deja.com...
> > Hi,
> > Here is my question, I have a table that include following info:
> >
> > ID Value
> > 1 250
> > 1 270
> > 1 250
> > 1 103
> > 1 100
> > 2 98
> > 2 76
> > 2 57
> > 2 34
> > 2 26
> > 2 12
> > 3 1000
> > 3 980
> > 3 460
> > 3 300
> > 3 137
> >
> > How can I extract the third largest value
> > with each ID, the result I wish to have is:
> >
> > ID Value
> > 1 250
> > 2 57
> > 3 460
> >
> > Can someone help me with this issue? Thanks in advance.
> >
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
Received on Fri Nov 19 1999 - 14:59:13 CST