How to assign ranks to the query results in Oracle?

OracleSoftware & CodingProgramming

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;
raja
Published on 04-Dec-2020 10:51:05
Advertisements