CODEWITHSUNDEEP
sqloracle-database
Sanju Gajra
Sanju Gajra

Reputation: 67

How to select middle 80% rows in Oracle SQL with order by (top % is not working)

I tried top % but that is not working in Oracle SQL. Offset and fetch next are working but I am not able to give percentage.

What will be the best way to fetch middle 80% rows?

Any help will be appreciated, thanks!

Upvotes: 1

Views: 409

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

Middle 80%? That's between 10 and 90%, then? Let's suppose it is.

Sample data (Scott's EMP table), sorted by salary:

SQL> select ename, job, sal,
  2    rank() over (order by sal) rnk
  3  from emp order by sal;

ENAME      JOB              SAL        RNK
---------- --------- ---------- ----------
SMITH      CLERK            800          1
JAMES      CLERK            950          2
ADAMS      CLERK           1100          3
WARD       SALESMAN        1250          4
MARTIN     SALESMAN        1250          4
MILLER     CLERK           1300          6
TURNER     SALESMAN        1500          7
ALLEN      SALESMAN        1600          8
CLARK      MANAGER         2450          9
BLAKE      MANAGER         2850         10
JONES      MANAGER         2975         11
SCOTT      ANALYST         3000         12
FORD       ANALYST         3000         12
KING       PRESIDENT       5000         14

14 rows selected.

CTE ranks employees by their salaries; the final where clause returns rows for those of them who fall into that "middle" 80% (the pct column).

SQL> with temp as
  2    (select ename, job, sal,
  3       rank() over (order by sal) rnk,        -- rank rows by salary
  4       count(*) over (order by null) cnt      -- total number of rows
  5     from emp
  6    )
  7  select t.*,
  8    round(rnk / cnt * 100) pct                -- percentage
  9  from temp t
 10  where round(rnk / cnt * 100) between 10 and 90;

ENAME      JOB              SAL        RNK        CNT        PCT
---------- --------- ---------- ---------- ---------- ----------
JAMES      CLERK            950          2         14         14
ADAMS      CLERK           1100          3         14         21
WARD       SALESMAN        1250          4         14         29
MARTIN     SALESMAN        1250          4         14         29
MILLER     CLERK           1300          6         14         43
TURNER     SALESMAN        1500          7         14         50
ALLEN      SALESMAN        1600          8         14         57
CLARK      MANAGER         2450          9         14         64
BLAKE      MANAGER         2850         10         14         71
JONES      MANAGER         2975         11         14         79
SCOTT      ANALYST         3000         12         14         86
FORD       ANALYST         3000         12         14         86

12 rows selected.

SQL>

Upvotes: 2

Related Questions