Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: group by, order by and rownum

Re: group by, order by and rownum

From: Christopher Beck <christopher.beck_at_oracle.com>
Date: Tue, 13 Aug 2002 16:18:29 -0400
Message-ID: <qSd69.19$Qi6.138@news.oracle.com>

Brian,

If I understand you correctly, then how about this for a solution:

ops$clbeck_at_ORA8I.WORLD> desc foo

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 ID                                                 NUMBER
 PID                                                NUMBER
 VALUE                                              VARCHAR2(10)

ops$clbeck_at_ORA8I.WORLD> select * from foo;

        ID PID VALUE
---------- ---------- ----------

       100          1 A
       101          1 B
       200          2 C
       201          2 D
       202          2 E
       203          2 F

6 rows selected.

ops$clbeck_at_ORA8I.WORLD> select value
  2 from ( select value,

  3                  row_number() over ( partition by pid order by id ) r
  4             from foo )

  5 where r = 2
  6 /

VALUE



B
D

hope this helps.

chris.

--

--
Christopher Beck, Principal Technologist, Oracle Corporation,
christopher.beck_at_oracle.com
Beginning Oracle Programming,
http://www.amazon.com/exec/obidos/ASIN/186100690X


"Brian Murphy" <rudie_at_wpi.edu> wrote in message
news:Pine.OSF.4.43.0208131400150.9088-100000_at_grover.WPI.EDU...

> Here's the scenario:
>
> I have a table with ids, parent_ids, and values. What I want to do is
>
> Group all tuples with the same parent id
> Order by over the ids
> return the 2nd tuple for each group (via Rownum, I think)
>
> After trying to get this to work, I've stumbled and cannot figure out how
> to go about it. Is it even possible to use rownums and groupbys in that
> fashion?
>
> thanks.
>
> brian
>
>
>
Received on Tue Aug 13 2002 - 15:18:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US