Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: group by, order by and rownum
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 )
VALUE
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...Received on Tue Aug 13 2002 - 15:18:29 CDT
> 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
>
>
>