Difference between rank dense rank and row number in SQL — Transcript

Full transcript

1. 0:00this is Part 111 of SQL Server tutorial
2. 0:03in this video we'll discuss the
3. 0:04difference between rank dense Rank and
4. 0:07row number functions in SQL Server first
5. 0:10let's look at the similarities between
6. 0:11these three functions all these three
7. 0:13functions return an increasing integer
8. 0:16value starting at one based on the
9. 0:18ordering of rows imposed by the order by
10. 0:20Clause if there are no ties if there are
11. 0:23ties then the rules are a bit different
12. 0:25which we will discuss in just a bit for
13. 0:28all these three functions Auto by Clause
14. 0:30is required Partition by Clause is
15. 0:32optional when the data is partition the
16. 0:35integer value is reset to one when the
17. 0:37partition changes so all these are the
18. 0:39similarities and another important thing
19. 0:41to keep in mind is that all these three
20. 0:44functions are going to behave exactly
21. 0:46the same way if we don't have duplicate
22. 0:49values in the column that we are using
23. 0:51in the order by Clause so if you look at
24. 0:54these three functions here row number
25. 0:55rank and dense rank we know that for all
26. 0:58these three functions autoby Clause is
27. 1:00required so we are using the autoby
28. 1:02clause and then we are sorting the data
29. 1:05by salary column in descending order and
30. 1:08if you look at that salary column in
31. 1:10this table notice that we don't have any
32. 1:12duplicate values in that column and if
33. 1:15you look at what data this query
34. 1:17produces look at the row number rank and
35. 1:20dense rank functions all three of them
36. 1:23produce the same numbering
37. 1:25sequence that's how the behavior is
38. 1:28going to be if you don't have have
39. 1:30duplicate values in the column that you
40. 1:33have used in the aut by Clause of these
41. 1:36three
42. 1:37functions now I have already typed this
43. 1:39exact same query here look at this when
44. 1:41we execute that we get the same
45. 1:44numbering sequence for all the three
46. 1:46functions that we have seen on the slide
47. 1:48if you want to see the difference
48. 1:50between these three functions then we
49. 1:52need to have some duplicate data in the
50. 1:55column that we have used in the aut by
51. 1:57Clause so to get some duplicate data
52. 2:00what I'm going to do is delete the data
53. 2:02that we already have in this employees
54. 2:05table and then I'm going to execute this
55. 2:07insert script right here and if you look
56. 2:10at this insert script notice that for
57. 2:11the first three rows we are inserting
58. 2:13same salary
59. 2:158,000 now let's go ahead and execute the
60. 2:19same query and look at the result we get
61. 2:22we get a different result now let's see
62. 2:25that in this PowerPoint slide we can
63. 2:27actually see it better here now look at
64. 2:29this for the first plus three rows we
65. 2:31have the same salary 8,000 so there is a
66. 2:34tie between those three rows in this
67. 2:37case row number function is still going
68. 2:39to do it the same way as it has been
69. 2:42doing it's going to assign an increasing
70. 2:45unique number for each row starting at
71. 2:48one so it starts from one goes all the
72. 2:50way till five even if there are ties
73. 2:53whereas if you look at the rank function
74. 2:55for the tide rows that is for the
75. 2:57duplicate rows it's giving the same rank
76. 2:59one
77. 3:00okay and then if you look at the row
78. 3:02that is present after the tide rows look
79. 3:04at that it didn't give it rank two it
80. 3:07has skipped rank two three and for this
81. 3:11third row it has given I mean fourth row
82. 3:13it has given a rank of
83. 3:14four okay that means rank function is
84. 3:18going to skip rankings if there are
85. 3:21duplicate rows if you look at dense rank
86. 3:24function this function just like rank
87. 3:26function it's going to assign the same
88. 3:28rank where we have ties so in this case
89. 3:31it has assigned one for the first three
90. 3:34rows and then for the third fourth row
91. 3:37that is present after the tide rows it
92. 3:40has given a rank of two it didn't skip
93. 3:43any rank so that's the fundamental
94. 3:45difference between these three functions
95. 3:47so here is the difference row number
96. 3:50returns an increasing unique number for
97. 3:52each row starting at one even if there
98. 3:54are duplicates rank function returns an
99. 3:57increasing unique number for each row
100. 3:59starting at one
101. 4:00when there are duplicates same rank is
102. 4:02assigned to all the duplicate rows but
103. 4:04the next row after the duplicate rows
104. 4:07will have the rank it would have been
105. 4:09assigned if there had been no duplicates
106. 4:12so rank function skips rankings if there
107. 4:14are duplicates dense rank returns an
108. 4:17increasing unique number for each row
109. 4:20starting at one just like the rank
110. 4:21function when there are duplicates same
111. 4:24rank is assigned to all the duplicate
112. 4:25rows but the dense rank function will
113. 4:28not skip any rank ranks this means the
114. 4:31next row after the duplicate rows will
115. 4:34have the next rank in the
116. 4:37sequence thank you for listening and
117. 4:39have a great day