Posted By

bbrumm on 02/03/17

Tagged

sql
Oracle

Versions (?)

Last Edited at 02/03/17 07:21am

Statistics

Viewed 733 times

Favorited by 0 user(s)

Related snippets

Oracle DENSE_RANK Function

/ Published in: SQL

A few examples of the DENSE_RANK function.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

/*
DENSE_RANK
*/
 
SELECT * FROM student ORDER BY fees_paid;
 
 
--Example 1
SELECT
DENSE_RANK(100) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;
 
--Example 2
SELECT
DENSE_RANK(150) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;
 
 
--Example 3
SELECT
DENSE_RANK(150, 'Minson') WITHIN GROUP (ORDER BY fees_paid, last_name) AS rank_val
FROM student;
 
 
--Example 4
SELECT
DENSE_RANK(150) WITHIN GROUP (ORDER BY fees_paid NULLS FIRST) AS rank_val
FROM student;
 
 
--Example 5
SELECT
DENSE_RANK('Julie') WITHIN GROUP (ORDER BY fees_paid) AS rank_val
FROM student;
 
 
 
--Example 6
SELECT
DENSE_RANK('Julie') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;
 
 
--Example 7
SELECT
DENSE_RANK('Boris') WITHIN GROUP (ORDER BY first_name) AS rank_val
FROM student;
 
SELECT * FROM student;
 
UPDATE student
SET gender = 'F'
WHERE first_name IN ('Susan', 'Julie', 'Michelle', 'Tanya');
 
UPDATE student
SET gender = 'M'
WHERE gender IS NULL;
 
 
 
--Example 8
SELECT
student_id, first_name, last_name, gender, fees_paid,
DENSE_RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val
FROM student;
 
 
 
 
--Example 9
SELECT
student_id, first_name, last_name, gender, fees_paid,
DENSE_RANK() OVER (PARTITION BY fees_paid ORDER BY last_name, first_name) AS rank_val
FROM student;
 
 
 
--Example 10
SELECT
student_id, first_name, last_name, gender, fees_paid,
DENSE_RANK() OVER (ORDER BY fees_paid) AS rank_val
FROM student;
 
 
--Example 11
SELECT
student_id, first_name, last_name, gender, fees_paid,
RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val,
DENSE_RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS dense_rank_val
FROM student;