Home » RDBMS Server » Server Administration » Top N records
Top N records [message #374925] Wed, 11 July 2001 19:21 Go to next message
Ravi Panyam
Messages: 2
Registered: July 2001
Junior Member
I have a table with 2 columns x and amt. For each value of x, there can be multiple values of amount.

My requirement is that I need to select the top 3 records for each value of x, in descending order of amount.

Is this possible without using a cursor on x?

Thanks.
Re: Top N records [message #374937 is a reply to message #374925] Thu, 12 July 2001 07:15 Go to previous messageGo to next message
Hans
Messages: 42
Registered: September 2000
Member
Here is my statement for Oracle 8.1.5 and 8.1.6.
But Oracle 8.1.7 introduced new <b>analytical</b> functions
which are designed for this kind of problems .
 
drop table testtab;
create table testtab (
   x     number(3),
   n     number(10)
);
 
insert into testtab values ( 1, 3 );
insert into testtab values ( 1, 30 );
insert into testtab values ( 1, 8 );
insert into testtab values ( 1, -8 );
insert into testtab values ( 1, 12 );
insert into testtab values ( 1, 15 );
 
insert into testtab values ( 2, 3 );
insert into testtab values ( 2, 14 );
insert into testtab values ( 2, 5 );
insert into testtab values ( 2, 18 );
insert into testtab values ( 2, 12 );
insert into testtab values ( 2, 15 );
 
insert into testtab values ( 3, 5 );
insert into testtab values ( 3, 0 );
 
insert into testtab values ( 4, 5 );
insert into testtab values ( 4, 0 );
insert into testtab values ( 4, 4 );
insert into testtab values ( 4, 5 );
 
-- all values
select * from testtab
   order by 1,2 desc;
   
         X          N
---------- ----------
         1         30
         1         15
         1         12
         1          8
         1          3
         1         -8
         2         18
         2         15
         2         14
         2         12
         2          5
         2          3
         3          5
         3          0
         4          5
         4          5
         4          4
         4          0
 

-- my solution                 
select x, n from (
   select x, n, rn from (
      select x, n, rownum rn from (
         select x, n from testtab
            order by x, n desc
      ) 
   ) a 
   where a.rn = 
      ( 
         select min(rn) from (
            select x, n, rownum rn from (
               select x, n from testtab
                  order by x, n desc
                  ) 
            ) b
            where b.x = a.x
      )  
   union all
   select x, n, rn from (
      select x, n, rownum rn from (
         select x, n from testtab
            order by x, n desc
      ) 
   ) a 
   where a.rn - 1 = 
      ( 
         select min(rn) from (
            select x, n, rownum rn from (
               select x, n from testtab
                  order by x, n desc
                  ) 
            ) b
            where b.x = a.x
      )  
   union all
   select x, n, rn from (
      select x, n, rownum rn from (
         select x, n from testtab
            order by x, n desc
      ) 
   ) a 
   where a.rn - 2 = 
      ( 
         select min(rn) from (
            select x, n, rownum rn from (
               select x, n from testtab
                  order by x, n desc
                  ) 
            ) b
            where b.x = a.x
      )  
) 
order by 1, 2 desc;
 

         X          N
---------- ----------
         1         30
         1         15
         1         12
         2         18
         2         15
         2         14
         3          5
         3          0
         4          5
         4          5
         4          4
Re: Top N records [message #374963 is a reply to message #374937] Sat, 14 July 2001 01:31 Go to previous messageGo to next message
smalltree
Messages: 1
Registered: July 2001
Junior Member
My solution is:
select x,n from testtab a
where 3>= (select count(*) from
testtab b where a.x = b.x and b.n >= a.n)
Re: Top N records [message #375008 is a reply to message #374963] Wed, 18 July 2001 14:56 Go to previous message
Ravi Panyam
Messages: 2
Registered: July 2001
Junior Member
The analytical functions in oracle work in 8.1.6 also. So the following also works -

select x, n from
(select x, n, row_number() over (partition by x order by n desc) as RK from testtab) where RK < 4;

Functions like rank and dense_rank also work similar to row_number.
Previous Topic: database triggers b/n a master and detail table
Next Topic: Report and Save
Goto Forum:
  


Current Time: Sun Jul 07 16:10:34 CDT 2024