Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to assign ranks to the query results in Oracle?
Problem Statement:
You want to assign a number/rank representing their positions in the result.
Solution:
Oracle provides the RANK analytic function to generate a ranking number for rows in a result set. To demonstrate we will rank students by fees, from highest paid down. The following SELECT statement uses the rank function to assign these values.
Example
SELECT student_id, first_name, last_name, fees, RANK() OVER (ORDER BY fees DESC) AS rnk FROM students;
Output
| student_id | first_name | last_name | fees | rnk |
| 100 | SMITH | JAMES | 24000 | 1 |
| 101 | JOHNSON | JOHN | 17000 | 2 |
| 102 | WILLIAMS | ROBERT | 17000 | 2 |
| 108 | RODRIGUEZ | JOSEPH | 12008 | 4 |
| 103 | BROWN | MICHAEL | 9000 | 5 |
| 109 | WILSON | THOMAS | 9000 | 5 |
| 110 | MARTINEZ | CHRISTOPHER | 8200 | 7 |
| 112 | TAYLOR | PAUL | 7800 | 8 |
| 111 | ANDERSON | DANIEL | 7700 | 9 |
| 113 | THOMAS | MARK | 6900 | 10 |
| 104 | JONES | WILLIAM | 6000 | 11 |
| 105 | MILLER | DAVID | 4800 | 12 |
| 106 | DAVIS | RICHARD | 4800 | 12 |
| 107 | GARCIA | CHARLES | 4200 | 14 |
RANK behaves similar to any other analytic function, operating in a second pass over the result set once non analytic processing is complete. In the above SQL, the results are processed in descending order by fees, and computes the rank value on the results starting at 1.
Couple of students with similar fees 12008, has a rank of 4. This is known as sparse ranking, where same values are ranked same. This may or may not be a desired outcome depending on your requirements as few of the ranks are missing.
An alternative to sparse ranking is called dense ranking. We will observe the results with dense_rank function.
Example
SELECT student_id, first_name, last_name, fees, dense_rank() OVER (ORDER BY fees DESC) AS rnk FROM students
Output
| student_id | first_name | last_name | fees | rnk |
| 100 | SMITH | JAMES | 24000 | 1 |
| 101 | JOHNSON | JOHN | 17000 | 2 |
| 102 | WILLIAMS | ROBERT | 17000 | 2 |
| 108 | RODRIGUEZ | JOSEPH | 12008 | 3 |
| 103 | BROWN | MICHAEL | 9000 | 4 |
| 109 | WILSON | THOMAS | 9000 | 4 |
| 110 | MARTINEZ | CHRISTOPHER | 8200 | 5 |
| 112 | TAYLOR | PAUL | 7800 | 6 |
| 111 | ANDERSON | DANIEL | 7700 | 7 |
| 113 | THOMAS | MARK | 6900 | 8 |
| 104 | JONES | WILLIAM | 6000 | 9 |
| 105 | MILLER | DAVID | 4800 | 10 |
| 106 | DAVIS | RICHARD | 4800 | 10 |
| 107 | GARCIA | CHARLES | 4200 | 11 |
We can now see the missing consecutive rank values.
Data Preparation: Data used for the problem is shown below.
Example
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
Example
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
339 Views
