Home » RDBMS Server » Server Administration » Top N records
Top N records [message #374925] |
Wed, 11 July 2001 19:21 |
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 |
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 #375008 is a reply to message #374963] |
Wed, 18 July 2001 14:56 |
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.
|
|
|
Goto Forum:
Current Time: Sat Jan 25 17:06:08 CST 2025
|