Hi Guys,
I am having below table
| ID | Am_1 | Am_2 | Am_3 |
| 1 | 20 | | 20 |
| 2 | | 30 | 31 |
| 3 | 15 | 15 | 15 |
| 4 | 10 | 10 | |
| | | |
I need to take the top 20 percentage of values based on each Am_1,am_2,am_3 columns. For eg sort am_1 in desc and get top 2 id's
Output required
| ID | Am_1 | Am_2 | Am_3 |
| 1 | 20 | | 20 |
| 2 | | 30 | 31 |
| 3 | 15 | 15 | |
| 4 | | | |
Table Queries
create table source_table(id number,am_1 number,am_2 number,am_3 number);
insert into table source_table values (1,20,'',20);
insert into table source_table values (2,'',30,31);
insert into table source_table values (3,15,15,15);
insert into table source_table values (4,10,10,'');
> select * from source_table;
1 20 NULL 20
2 NULL 30 31
3 15 15 15
4 10 10 NULL
Query i have used:
SELECT t.n_cust,
t.am_1
FROM (
SELECT a.id,
a.am_1,
row_number() over (ORDER BY cast(a.am_1 AS DECIMAL(8,7)) DESC) AS rank
FROM (
SELECT id,
am_1
FROM source_table
ORDER BY am_1 DESC ) a) t
join
(
SELECT count(1) AS count
FROM source_table
AND am_1 is not null )f
WHERE t.rank < 0.2*f.count
ORDER BY am_1 DESC;
Assuming that there are more than 10 records so that top 20 percentage would be 2 values.