Shulou(Shulou.com)06/02 Report--

This article introduces how to achieve juxtaposition ranking display in Oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

First: dense_rank () over (order by field ascending or descending) as alias from table name; it is convenient to use aliases and filter conditions. When ranking, the same person is counted side by side, for example, select ename,job,sal,dense_rank () over (order by sal desc) as rank from emp ENAME JOB SAL RANK--KING PRESIDENT 5000 1FORD ANALYST 3000 2SCOTT ANALYST 3000 2JONES MANAGER 2975 3BLAKE MANAGER 2850 4CLARK MANAGER 2450 5ALLEN SALESMAN 1600 6TURNER SALESMAN 1500 7ADAMS CLERK 1400 8SMITH CLERK 1400 8MILLER CLERK 1300 9WARD SALESMAN 1250 10MARTIN SALESMAN 1250 10JAMES CLERK 95011 14 rows have been selected. For example, for employees whose restrictions are ranked second, the juxtaposition shows: SQL > select * from (select ename,job,sal,dense_rank () over (order by sal desc) as rank from emp) where rank = 2 ENAME JOB SAL RANK--SCOTT ANALYST 3000 2FORD ANALYST 3000 2 second: rank () over (order by field name ascending or descending) as alias from table name When ranking, no matter how many people are juxtaposed, no matter how many serial numbers are counted, such as: select ename,job,sal,rank () over (order by sal desc) as rank from emp ENAME JOB SAL RANK--KING PRESIDENT 5000 1FORD ANALYST 3000 2SCOTT ANALYST 3000 2JONES MANAGER 2975 4BLAKE MANAGER 2850 5CLARK MANAGER 2450 6ALLEN SALESMAN 1600 7TURNER SALESMAN 1500 8ADAMS CLERK 1400 9SMITH CLERK 1400 9MILLER CLERK 1300 11WARD SALESMAN 1250 12MARTIN SALESMAN 1250 12JAMES CLERK 95014 14 have selected 14 rows. The third kind: row_number () over (order by field name ascending or descending order) as alias from table name; this kind is ranked directly by sequence number, regardless of juxtaposition, such as: 1, 2, 3, 4 SQL > select ename,job,sal,row_number () over (order by sal desc) as rank from emp ENAME JOB SAL RANK--KING PRESIDENT 5000 1FORD ANALYST 3000 2SCOTT ANALYST 3000 3JONES MANAGER 2975 4BLAKE MANAGER 2850 5CLARK MANAGER 2450 6ALLEN SALESMAN 1600 7TURNER SALESMAN 1500 8ADAMS CLERK 1400 9SMITH CLERK 1400 10MILLER CLERK 1300 11WARD SALESMAN 1250 12MARTIN SALESMAN 1250 13JAMES CLERK 95014 14 have selected 14 rows. On how to achieve juxtaposition ranking display in Oracle to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.