Showing posts with label select top percentiles in Oracle. Show all posts
Showing posts with label select top percentiles in Oracle. Show all posts

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;