Rank and Dense Rank in SQL Server — Transcript

Full transcript

1. 0:00this is part 110 of SQL Server tutorial
2. 0:03in this video we'll discuss Rank and
3. 0:05dense rank functions in SQL Server Rank
4. 0:08and dense rank functions are introduced
5. 0:10in SQL Server 2005 both of these
6. 0:12functions return a rank starting at one
7. 0:15based on the ordering of rows imposed by
8. 0:17the order by Clause order by Clause is
9. 0:19required Partition by Clause is optional
10. 0:22when the data is partition rank is reset
11. 0:25to one when the partition changes now we
12. 0:28have two different functions to provide
13. 0:30a rank so what is the difference between
14. 0:32Rank and dense rank functions rank
15. 0:35function skips rankings if there is a
16. 0:37tie whereas dense rank will not let's
17. 0:40understand this with an example let's
18. 0:42say we have two rows at rank one and we
19. 0:44have five rows in total in the table in
20. 0:46this case rank function is going to give
21. 0:49a rank of one for the first two rows 1
22. 0:52one because there is a tie for the third
23. 0:54row look at that it gives a rank of
24. 0:56three it has skipped rank two meaning
25. 1:00rank function is going to leave holes in
26. 1:02the rankings it provide if there is a
27. 1:05tie dense rank on the other hand will
28. 1:08not leave those holes so just like rank
29. 1:11function dense rank also gives a rank of
30. 1:14one to the first two rows and then for
31. 1:16the third row it's actually giving a
32. 1:18rank of two instead of three so that's
33. 1:21the fundamental difference between Rank
34. 1:23and dense rank functions and we have the
35. 1:25syntax here we first specify the name of
36. 1:27the function rank or dense rank followed
37. 1:30by that we use the over keyword and then
38. 1:32within parenthesis we use the auto by
39. 1:34clause and then the column list by which
40. 1:36we want to sort the data let's look at
41. 1:38an example and that should make all
42. 1:40these points
43. 1:41clear we'll use this employees table for
44. 1:44the examples in this sto what we want to
45. 1:46do is sort the employees data by salary
46. 1:49column in descending order and then
47. 1:51provide ranks for every row so we
48. 1:54basically want to provide a rank of one
49. 1:55to the first highest salary rank of two
50. 1:57to the second highest salary so on and
51. 2:00so forth within our result set we want
52. 2:06name
53. 2:08gender salary and rank so let's use the
54. 2:12rank function so we first specify the
55. 2:15name of the function followed by that we
56. 2:17use the over keyword and then within
57. 2:18parenthesis we use utob by Clause utob
58. 2:21by Clause is required because Rank and
59. 2:24dense rank are issued based on the
60. 2:27ordering imposed by this utob by clause
61. 2:30so we want to sort the data by salary
62. 2:32column in descending order and let's
63. 2:36give this column an alas let's call this
64. 2:38rank since rank is a function name let's
65. 2:41wrap that with a pair of square brackets
66. 2:45and let's do the same thing for dense
67. 2:47rank so I'm going to use dense rank
68. 2:50function as well and let's change the
69. 2:53column name to dense rank let's execute
70. 2:58the query and see what we get now the
71. 3:00first thing to keep in mind here is that
72. 3:01we are not using Partition by Clause so
73. 3:05the data is not partition we have a
74. 3:06single partition here now look at what
75. 3:10rank and dense rank functions does for
76. 3:12the first two rows both the functions
77. 3:15give a rank of one and then for the
78. 3:17third row rank function gives a rank of
79. 3:20three whereas Den rank gives a rank of
80. 3:23two so rank function is going to leave
81. 3:26holes in the rankings it provides if
82. 3:29there is a
83. 3:30dense rank on the other hand will not do
84. 3:32that it doesn't leave any holes so here
85. 3:36we don't have a partition
86. 3:38involved and we have that exact same
87. 3:40example right here now let's see what's
88. 3:43going to happen if there is a partition
89. 3:45involved what we want to do now is
90. 3:47partition this employee table data based
91. 3:50on the gender column when we do that we
92. 3:52are going to get two partitions male
93. 3:54employee partition and female employee
94. 3:55partition so let's use the Partition by
95. 3:58class so Partition
96. 4:02by gender let's do the same thing for
97. 4:04dense rank as
98. 4:06well now let's execute this query and
99. 4:09see what we get the first thing to
100. 4:11notice here is that we've got two
101. 4:12partitions the first partition is female
102. 4:14employee partition and look at the
103. 4:16ranking it starts from one and goes till
104. 4:18four same for both Rank and T rank when
105. 4:21the partition changes look at what
106. 4:23happens to the rank it is reset to one
107. 4:26just like the r number function that we
108. 4:28discussed in our previous video video
109. 4:31session and we have that example right
110. 4:34here so the next obvious question that
111. 4:36comes to our mind is what is the use
112. 4:38case for Rank and dense rank functions
113. 4:41well both of these functions can be used
114. 4:43to find nth highest salary however which
115. 4:47function to use depends on what you want
116. 4:49to do when there is a time for example
117. 4:53if we have two employees with the first
118. 4:56highest salary then there could be two
119. 4:58possible business cas cases if your
120. 5:00gness case is to not return any rows for
121. 5:04the second highest salary then use rank
122. 5:07function on the other hand if your
123. 5:08business case is to return the row that
124. 5:11is present after the tide rows as the
125. 5:14second highest salary then use dense
126. 5:17rank function let's understand this with
127. 5:19an example so what I'm going to do is
128. 5:22modify this query So within our result
129. 5:24set I don't want name and gender columns
130. 5:26so I'm going to get rid of those two
131. 5:27columns we just want salary and then for
132. 5:30now I'm going to use rank function I'm
133. 5:32going to get rid of this Partition by
134. 5:34Clause just use Auto by and let's call
135. 5:38this
136. 5:39column salary
137. 5:42Rank and let's remove this T rank
138. 5:45function
139. 5:47now now whatever result we get when we
140. 5:50execute this query I'm going to make it
141. 5:52a part of a CDA Common Table expression
142. 5:56so with let's call this result
143. 6:05as so that's our CTE there now what I
144. 6:09want to
145. 6:10do is
146. 6:14select top
147. 6:18one
148. 6:20salary
149. 6:22from our result
150. 6:25City
151. 6:27where rank equals 1 now what I'm going
152. 6:31to do is just about this I'm going to
153. 6:33include another query select star from
154. 6:36employees order
155. 6:38buy salary descending
156. 6:42order so let's execute this and see what
157. 6:45is the highest salary so highest salary
158. 6:47is 8,000 now if we want to find the
159. 6:50highest salary we can use this you know
160. 6:52the first highest
161. 6:54salary so actually the column name is
162. 6:56salary rank not
163. 6:58rank
164. 7:00where salary rank is one so when we
165. 7:02execute this we get the first highest
166. 7:04salary without any problem but when I
167. 7:07say I want the second highest
168. 7:10salary look at what I'm going to get I
169. 7:13get no row if your business case is this
170. 7:16if there is a tie then you know here the
171. 7:19tie is for the first highest salary that
172. 7:21means we are not going to have the
173. 7:23second highest salary if we use the rank
174. 7:26function and that is what your business
175. 7:28requirement is then use the rank
176. 7:30function return no rows if there is a
177. 7:32tie for the first rank for the second
178. 7:34highest salary okay and and basically
179. 7:37that's because rank function leaves
180. 7:41holes so let's execute this query look
181. 7:44at that we don't have a salary rank of
182. 7:46two so that's why it returns no Row for
183. 7:49the second highest salary on the other
184. 7:51hand if your requirement is you know
185. 7:54well even if there is a tie you know in
186. 7:56this case the tie is for the first two
187. 7:58rows I mean for the first highest salary
188. 8:01so but still for the third row I want to
189. 8:03treat that as the uh row with the second
190. 8:06rank in that case use dense rank
191. 8:11function so when I use dense Rank and
192. 8:15when we execute this we're going to get
193. 8:197,000 as the second highest
194. 8:22salary okay so depending on your
195. 8:25business requirement you can use rank or
196. 8:27dense rank
197. 8:30now we can also use these Rank and dense
198. 8:33rank functions to find the nth highest
199. 8:35salary among male or female employee
200. 8:37groups and by now you might have already
201. 8:39guessed the way we do that is by using
202. 8:42Partition by Clause so if you want to
203. 8:44find for example let's say the third
204. 8:47highest salary amount paid among the
205. 8:49female employee group we can very easily
206. 8:52achieve that by using Partition by
207. 8:56class so Partition by let's partition
208. 9:01the data by gender
209. 9:07column first let's execute this query on
210. 9:10its own so this is going to partition
211. 9:12the data look at that you know this is
212. 9:16the female employee group we can very
213. 9:18easily that if we see that if we include
214. 9:20the gender column and this is the male
215. 9:22employee group so among the female
216. 9:24employee group if you look at the
217. 9:26salaries the third highest salary is
218. 9:294,500 so I can say select top one salary
219. 9:33from results so we need gender column as
220. 9:35well within our result set so I'm going
221. 9:37to include
222. 9:39that so select top one salary from
223. 9:41result where salary rank equals three
224. 9:47and gender
225. 9:50equals
226. 9:52female okay so when we do this we should
227. 9:56get
228. 9:584,500 there we
229. 10:02go thank you for listening and have a
230. 10:04great
231. 10:06day