OracleTuts Oracle Training, Tutorials, and Videos from Beginner to Advanced
A long time ago I put together a tutorial (How To Use Oracle Analytic Functions in Oracle SQL) on how to use some of the more common Oracle Analytic Functions. In this tutorial I am going to discuss how to rank records in Oracle using the RANK() and the DENSE_RANK() analytic functions. I chose to put this in a separate post, since it is more advanced than the other analytic functions I covered in the mentioned previous post.
Introduction
In the examples in this little tutorial I will be using the tables found in the “OE” (Order Entry) sample schema that comes with most Oracle Installations. You can i.e. read more about it here: Oracle® Database Sample Schemas
Ok, so let us pretend that we have gotten the task to look at the data for a given year, and then rank the customers with the highest order total, and we want to do this for each month.
Example # 1: Using the RANK() analytic function
If you want to present your data with people having the same order total show as ties, RANK() is the analytic function you should use.
The syntax for using the RANK() analytic function is as follows:
RANK() OVER ( [ query_partition_clause] ORDER BY clause )
SQL> set pagesize 500
SQL>
SQL> SELECT x.customer_id
2 ,x.mnth
3 ,x.order_total
4 ,RANK() OVER (PARTITION BY x.mnth ORDER BY x.order_total DESC) month_rank
5 FROM (
6 SELECT o.customer_id
7 ,EXTRACT (MONTH FROM o.order_date) mnth
8 ,ROUND(SUM(o.order_total), -3) order_total
9 FROM oe.orders o
10 WHERE EXTRACT (YEAR FROM o.order_date) = 2007
11 GROUP BY o.customer_id, EXTRACT (MONTH FROM o.order_date)
12 ) x
13 ORDER BY x.mnth, 4 ASC
14 ;
CUSTOMER_ID MNTH ORDER_TOTAL MONTH_RANK
----------- ---------- ----------- ----------
122 2 104000 1
119 2 16000 2
109 3 73000 1
149 3 53000 2
107 3 32000 3
108 3 30000 4
102 3 11000 5
105 3 2000 6
104 3 0 7
147 4 2000 1
118 5 79000 1
143 5 27000 2
142 5 26000 3
146 5 18000 4
169 5 16000 5
150 6 283000 1
141 6 38000 2
123 6 11000 3
116 6 11000 3
165 6 3000 5
148 6 3000 5
145 6 1000 7
167 6 0 8
146 6 0 8
166 6 0 8
117 7 104000 1
109 7 78000 2
159 7 69000 3
154 7 27000 4
163 7 1000 5
161 7 1000 5
160 7 1000 5
164 7 1000 5
162 7 0 9
101 8 78000 1
107 8 71000 2
147 8 37000 3
146 8 27000 4
105 8 22000 5
145 8 10000 6
149 9 269000 1
144 9 62000 2
145 9 14000 3
102 9 11000 4
116 9 6000 5
104 9 5000 6
103 9 0 7
101 10 30000 1
118 10 22000 2
149 10 13000 3
144 10 10000 4
103 10 7000 5
116 10 0 6
108 11 120000 1
109 11 93000 2
117 11 50000 3
102 11 42000 4
158 11 25000 5
116 11 15000 6
146 11 14000 7
145 11 10000 8
149 11 9000 9
152 11 8000 10
157 11 7000 11
107 11 1000 12
147 12 296000 1
148 12 32000 2
144 12 11000 3
68 rows selected
NOTE: To be able to show you how the data ties, I wrapped the ROUND function around the order_total field like this: ROUND(SUM(o.order_total), -3) .
As you can see, we have some ties. Take a look at i.e. month # 6, and month # 7.
Also, if you look at month # 7, you will see that the rank numbers are consecutive:
SQL> SELECT x.customer_id
2 ,x.mnth
3 ,x.order_total
4 ,RANK() OVER (PARTITION BY x.mnth ORDER BY x.order_total DESC) month_rank
5 FROM (
6 SELECT o.customer_id
7 ,EXTRACT (MONTH FROM o.order_date) mnth
8 ,ROUND(SUM(o.order_total), -3) order_total
9 FROM oe.orders o
10 WHERE EXTRACT (YEAR FROM o.order_date) = 2007
11 GROUP BY o.customer_id, EXTRACT (MONTH FROM o.order_date)
12 ) x
13 WHERE x.mnth = 7
14 ORDER BY x.mnth, 4 ASC
15 ;
CUSTOMER_ID MNTH ORDER_TOTAL MONTH_RANK
----------- ---------- ----------- ----------
117 7 104000 1
109 7 78000 2
159 7 69000 3
154 7 27000 4
163 7 1000 5
161 7 1000 5
160 7 1000 5
164 7 1000 5
162 7 0 9
9 rows selected
The rank number show # 4, then four of rank # 5, and then rank # 9.
Example # 2: Using the DENSE_RANK() analytic function
Some times you do not want to show the rank numbers as consecutive. If this is the case for you, you should use the DENSE_RANK() analytic function.
The syntax for using the DENSE_RANK() analytic function is as follows:
DENSE_RANK() OVER ( [ query_partition_clause] ORDER BY clause )
SQL> SELECT x.customer_id
2 ,x.mnth
3 ,x.order_total
4 ,DENSE_RANK() OVER (PARTITION BY x.mnth ORDER BY x.order_total DESC) month_rank
5 FROM (
6 SELECT o.customer_id
7 ,EXTRACT (MONTH FROM o.order_date) mnth
8 ,ROUND(SUM(o.order_total), -3) order_total
9 FROM oe.orders o
10 WHERE EXTRACT (YEAR FROM o.order_date) = 2007
11 GROUP BY o.customer_id, EXTRACT (MONTH FROM o.order_date)
12 ) x
13 WHERE x.mnth = 7
14 ORDER BY x.mnth, 4 ASC
15 ;
CUSTOMER_ID MNTH ORDER_TOTAL MONTH_RANK
----------- ---------- ----------- ----------
117 7 104000 1
109 7 78000 2
159 7 69000 3
154 7 27000 4
163 7 1000 5
161 7 1000 5
160 7 1000 5
164 7 1000 5
162 7 0 6
9 rows selected
If you look at the rank numbers in example # 2, you will see they will go like this: # 4, four of rank # 5, and then rank # 6. The rank numbers are then non-consecutive.
Example # 3: What if I do not want to show any ties?
In some situations you will not want to show any ties at all in your list. How do we do this? One answer is to actually use the ROW_NUMBER() analytic function like this:
SQL> SELECT x.customer_id
2 ,x.mnth
3 ,x.order_total
4 ,ROW_NUMBER() OVER (PARTITION BY x.mnth ORDER BY x.order_total DESC) month_rank
5 FROM (
6 SELECT o.customer_id
7 ,EXTRACT (MONTH FROM o.order_date) mnth
8 ,ROUND(SUM(o.order_total), -3) order_total
9 FROM oe.orders o
10 WHERE EXTRACT (YEAR FROM o.order_date) = 2007
11 GROUP BY o.customer_id, EXTRACT (MONTH FROM o.order_date)
12 ) x
13 WHERE x.mnth = 7
14 ORDER BY x.mnth, 4 ASC
15 ;
CUSTOMER_ID MNTH ORDER_TOTAL MONTH_RANK
----------- ---------- ----------- ----------
117 7 104000 1
109 7 78000 2
159 7 69000 3
154 7 27000 4
163 7 1000 5
161 7 1000 6
160 7 1000 7
164 7 1000 8
162 7 0 9
9 rows selected
As you can see in example # 3: Mission accomplished – no ties. This example was kinda outside the scope of this post, but it also kinda belongs here.
A bit of cautiousness
I just wanted to add a note for example # 1 and 2. If you use the sql as shown in these examples, you might run into a situation where you get a different result each time you run it. The reason for this is that it is not described in the examples how to sort the people that ties. This might be ok for what you are doing, but if you are i.e. showing results for a contest for hundreds of distributors in a Multi Level Marketing (MLM) company, and the results keep on changing…I guarantee you: The phones will be chiming.
So, to solve this you can i.e. change the ORDER BY line to something like this:
ORDER BY x.mnth, 4 ASC, x.customer_id
This way you will always get the same result.
Conclusion
So, in conclusion we have the following rules for when we rank records in Oracle:
- If you want to show ties, and non-consecutive numbers: Use the RANK() analytic function
- If you want to show ties, and consecutive numbers: Use the DENSE_RANK() analytic function
- If you do not want to show ties at all, but consecutive numbers: Use the ROW_NUMBER analytic function
- Make sure you always show the same result
Hope this tutorial has helped you in your work. Don’t hesitate to leave a comment.
~ TJ
Pingback: How To Use Oracle Analytic Functions in Oracle SQL | ORACLETUTSOracleTuts