# 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.

## 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;