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;