Revision: 71272
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at February 3, 2017 07:21 by bbrumm
Initial Code
/* 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;
Initial URL
http://www.databasestar.com/oracle-dense_rank/
Initial Description
A few examples of the DENSE_RANK function.
Initial Title
Oracle DENSE_RANK Function
Initial Tags
sql, Oracle
Initial Language
SQL