hmiranda wrote:
> 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.
Glad it helped you.
--
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
Received on Thu Nov 30 2006 - 19:07:07 CST
