Thursday, August 30, 2012

Select top percent in Oracle

Oracle analytic function cume_dist() calculates the cumulative percentage for a variable.

The following query selects the top 5% records based on variable num.
with tbl as
(
select a.*, cume_dist() over(order by num desc) cumulative_pcnt from tbl_test a
)
select * from tbl where cumulative_pcnt <=0 .05;

The following query selects the bottom 5% records based on variable num.
with tbl as
(
select a.*, cume_dist() over(order by num ) cumulative_pcnt from tbl_test a
)
select * from tbl where cumulative_pcnt <=0 .05;

The following query selects the top 5% records based on variable num by group.
with tbl as
(
select a.*, cume_dist() over(partition by grp order by num desc) cumulative_pcnt from tbl_test a
)
select * from tbl where cumulative_pcnt <=0 .05;


No comments: