Row Number function in SQL Server — Transcript

Full transcript

1. 0:00this is part 109 of SQL Server tutorial
2. 0:03in this video we'll discuss R number
3. 0:05function in SQL Server this is
4. 0:07continuation to part 108 so please watch
5. 0:09part 108 before
6. 0:11proceeding row number function is
7. 0:13introduced in SQL Server 2005 it Returns
8. 0:16the sequential number of a row starting
9. 0:19at One Auto by Clause is required
10. 0:22Partition by Clause is optional when the
11. 0:24data is partitioned row number is reset
12. 0:27to one when the partition changes we
13. 0:29have the syntax here we first specify
14. 0:31the row number function followed by that
15. 0:34we use over keyword and then within
16. 0:36parenthesis we use the order by clause
17. 0:38and then the column list by which we
18. 0:40want to sort the data let's look at an
19. 0:42example and that should make all these
20. 0:44points clear let's flip to SQL
21. 0:46management Studio we'll use this
22. 0:48employees table notice within our result
23. 0:50set at the moment we have got three
24. 0:51columns name gender salary in addition
25. 0:54to these three columns I also want a row
26. 0:57number against every Row in the result
27. 1:00set so I'm going to use SQL Server
28. 1:02rowcor number function this function
29. 1:05does not take any parameters so followed
30. 1:08by the function name we use over keyword
31. 1:11and followed by that within parenthesis
32. 1:14we use order by Clause now let's sort
33. 1:18the data by gender column so order by
34. 1:22gender and let's give this column
35. 1:24andalas let's call this row number let's
36. 1:29execute this and look at that we get a
37. 1:31row number for every Row in the result
38. 1:34set starting from one now this order by
39. 1:38Clause is required if we don't specify
40. 1:41the order by Clause we get an error
41. 1:43saying the function rule number must
42. 1:45have an Over clause with order
43. 1:48by and the reason it requires order by
44. 1:51Clause is because this row number is
45. 1:53generated based on the order that is
46. 1:55imposed by this order by
47. 1:58Clause now at the moment we not using
48. 2:00Partition by let's see what's going to
49. 2:02happen when we use Partition by Clause
50. 2:05so let's partition the
51. 2:09data
52. 2:11by gender column so we are partitioning
53. 2:14the data by gender column ordering it by
54. 2:16gender column so let's execute this
55. 2:19query and see what result we get look at
56. 2:21that when the data is partitioned look
57. 2:23at the first four rows first so that's
58. 2:26the female employees partition so the
59. 2:29row number has started at 1 and it went
60. 2:31all the way till four and here we are
61. 2:33changing the partition from female to
62. 2:36male so at row number five look at what
63. 2:39happened to row number it is reset to
64. 2:42one and then it goes all the way until
65. 2:44six so whenever the partition changes
66. 2:46row number is reset to
67. 2:50one so here is the first example that we
68. 2:52discussed without partition row number
69. 2:54goes from 1 all the way till 10 and here
70. 2:57we have the example of partition ing
71. 3:00data and look at the row number 1 to 4
72. 3:02for female employees and 1:6 for male
73. 3:06employees now what is the use of this
74. 3:08row number function what's the use case
75. 3:10one of the excellent use cases is
76. 3:12deleting all duplicate rows except one
77. 3:15so if you have a situation where you
78. 3:17have a table with a lot of duplicate
79. 3:18rows and you want to delete all those
80. 3:20duplicate rows except one then we can
81. 3:23use row number function to very easily
82. 3:25achieve that we discussed this in detail
83. 3:28in part four of SQ server interview
84. 3:30questions and answers video series so if
85. 3:32you go to SQL server interview questions
86. 3:35and answers video series and then in
87. 3:38that part four is delete duplicate rows
88. 3:40in SQL and here is the associated text
89. 3:43article for that video so let's actually
90. 3:46quickly go over this example so at the
91. 3:49moment we already have employees table
92. 3:50so I'm going to delete this employees
93. 3:53table and use this script to create that
94. 3:57employees table once again so let's copy
95. 3:59the script and let's delete this
96. 4:02employees table
97. 4:04first and then we'll recreate that
98. 4:06employees
99. 4:13table so let's execute the script and
100. 4:17let's select the data from this table
101. 4:20Select Staff from
102. 4:22employees so notice we've got you know
103. 4:25several duplicate rows so what we want
104. 4:27to do is delete all the duplicates
105. 4:29except one and to do
106. 4:31[Applause]
107. 4:33that we are going to use this script
108. 4:36let's actually copy
109. 4:42this okay so look at the query here
110. 4:45which is part of this
111. 4:47CTE so when we execute this look at what
112. 4:50we get so we get all the data in the
113. 4:52employees table ID first name last name
114. 4:54gender and salary columns and then we
115. 4:57have a row number and look at how we are
116. 5:00using row number function here so select
117. 5:02star this is going to retrieve all the
118. 5:04columns from the employees table in
119. 5:06addition to all the columns we want row
120. 5:08number so we are using row number
121. 5:10function uh followed by that over
122. 5:12keyword Partition by ID column so if you
123. 5:15look at this ID column look at that you
124. 5:18know we have ID 1 and for ID one rows
125. 5:22look at the row number one to three and
126. 5:25for rows with ID 2 row number is reset
127. 5:27to one and it goes from 1 to two
128. 5:30and for this partition where ID is three
129. 5:33row number again starts at one and goes
130. 5:35all the way till three now so this is a
131. 5:40CTE a Common Table expression on that
132. 5:42CTE we are issuing a delete query so the
133. 5:45name of the CT is employee CT so delete
134. 5:48from employee CT where row number is
135. 5:51greater than one that means it's going
136. 5:54to delete wherever it finds row number
137. 5:57greater than one so it's going going to
138. 5:59delete these two rows so what happens
139. 6:02when it deletes those two rows we be
140. 6:04left with one row where ID is one
141. 6:07similarly it's going to delete the
142. 6:09second row for the second partition and
143. 6:11for the third partition it is going to
144. 6:13delete these two rows you know
145. 6:16effectively at the end of it it's going
146. 6:18to leave just one row in each partition
147. 6:21now let's say for example you want to
148. 6:23delete all rows except two duplicates
149. 6:26then you can simply change this
150. 6:28condition to
151. 6:30where row number greater than two so
152. 6:32what is it going to do in that case it's
153. 6:34going to delete all the rows where row
154. 6:36number is greater than two so from
155. 6:38partition one it's going to delete this
156. 6:40row and from partition two it's not
157. 6:42going to delete any row and from
158. 6:44partition three it's going to delete the
159. 6:46last row so let's actually execute this
160. 6:48query and see what we get so it has
161. 6:51deleted two rows from the table so when
162. 6:54we select from employees now look at
163. 6:55that we have two duplicates okay for
164. 6:59every partition now when we change the
165. 7:03condition to where row number greater
166. 7:04than one it should delete all the
167. 7:07duplicates except one now let's select
168. 7:10the
169. 7:12data and look at that so we don't have
170. 7:15duplicates
171. 7:18anymore thank you for listening and have
172. 7:20a great
173. 7:22day