Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Return limited rows from a table
This is funny. I assure you I made this example up myself, any
similarity is coincidental. Thank you for your help (and I apologize if
I gave away the answer).
DA Morgan wrote:
> Charles Hooper wrote:
> > DA Morgan wrote:
> >> hdmiranda_at_gmail.com wrote:
> >>> Hi
> >>> I am using Oracle 9i on Solaris.
> >>> I have a table as such:
> >>>
> >>> col1 col2 col3 col 4 value
> >>> a b c x 1
> >>> a b c y 2
> >>> a b c x 3
> >>> a b c g 4
> >>> d e f l 6
> >>> d e f m 3
> >>> d e f s 7
> >>> d e f j 9
> >>>
> >>> I need to return only 3 three rows for each key (col1, col2, col3) as
> >>> such:
> >>> col1 col2 col3 col 4 value
> >>> a b c x 1
> >>> a b c y 2
> >>> a b c x 3
> >>> d e f l 6
> >>> d e f m 3
> >>> d e f s 7
> >>>
> >>> Any suggestions appreciated.
> >>>
> >>> Thank you.
> >> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref965
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damorgan_at_x.washington.edu
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >> www.psoug.org
> >
> > That will help the OP a little, but I looked at that documentation a
> > couple times before the concepts started to make sense. It is a very
> > good recommendartion that the OP read every page of that section of the
> > documentation to understand how analytical functions work.
> >
> > I would suggest:
> > http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm
> > ROW_NUMBER, likely with a PARTITION BY col1, col2, col3 and ORDER BY
> > value. Then find a way to eliminate all entries where ROW_NUMBER
> > returns a value greater than 3.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>