Re: Return limited rows from a table

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. Received on Thu Nov 30 2006 - 13:29:15 CST