# Posted By

bbrumm on 02/03/17

# Statistics

Viewed 598 times
Favorited by 0 user(s)

# Oracle DENSE_RANK Function

/ Published in: SQL

A few examples of the DENSE_RANK function.

Copy this code and paste it in your HTML
1. /*
2. DENSE_RANK
3. */
4.
5. SELECT * FROM student ORDER BY fees_paid;
6.
7.
8. --Example 1
9. SELECT
10. DENSE_RANK(100) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
11. FROM student;
12.
13. --Example 2
14. SELECT
15. DENSE_RANK(150) WITHIN GROUP (ORDER BY fees_paid) AS rank_val
16. FROM student;
17.
18.
19. --Example 3
20. SELECT
21. DENSE_RANK(150, 'Minson') WITHIN GROUP (ORDER BY fees_paid, last_name) AS rank_val
22. FROM student;
23.
24.
25. --Example 4
26. SELECT
27. DENSE_RANK(150) WITHIN GROUP (ORDER BY fees_paid NULLS FIRST) AS rank_val
28. FROM student;
29.
30.
31. --Example 5
32. SELECT
33. DENSE_RANK('Julie') WITHIN GROUP (ORDER BY fees_paid) AS rank_val
34. FROM student;
35.
36.
37.
38. --Example 6
39. SELECT
40. DENSE_RANK('Julie') WITHIN GROUP (ORDER BY first_name) AS rank_val
41. FROM student;
42.
43.
44. --Example 7
45. SELECT
46. DENSE_RANK('Boris') WITHIN GROUP (ORDER BY first_name) AS rank_val
47. FROM student;
48.
49. SELECT * FROM student;
50.
51. UPDATE student
52. SET gender = 'F'
53. WHERE first_name IN ('Susan', 'Julie', 'Michelle', 'Tanya');
54.
55. UPDATE student
56. SET gender = 'M'
57. WHERE gender IS NULL;
58.
59.
60.
61. --Example 8
62. SELECT
63. student_id, first_name, last_name, gender, fees_paid,
64. DENSE_RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val
65. FROM student;
66.
67.
68.
69.
70. --Example 9
71. SELECT
72. student_id, first_name, last_name, gender, fees_paid,
73. DENSE_RANK() OVER (PARTITION BY fees_paid ORDER BY last_name, first_name) AS rank_val
74. FROM student;
75.
76.
77.
78. --Example 10
79. SELECT
80. student_id, first_name, last_name, gender, fees_paid,
81. DENSE_RANK() OVER (ORDER BY fees_paid) AS rank_val
82. FROM student;
83.
84.
85. --Example 11
86. SELECT
87. student_id, first_name, last_name, gender, fees_paid,
88. RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS rank_val,
89. DENSE_RANK() OVER (PARTITION BY gender ORDER BY fees_paid) AS dense_rank_val
90. FROM student;