Re: Return limited rows from a table

From: hmiranda <hdmiranda_at_gmail.com>
Date: 30 Nov 2006 15:06:45 -0800
Message-ID: <1164924776.020595.125980@h54g2000cwb.googlegroups.com>

Thank you for your help. This works:

select a, b, c, tval from (
select a, b, c, tval, row_number() over (partition by a, b,c order by a,b, c) r from testsort
)
where r < 4

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.
Received on Thu Nov 30 2006 - 17:06:45 CST