1
00:01.000 --> 00:01.320
Yeah.

2
00:01.320 --> 00:02.160
Hello, everyone.

3
00:02.560 --> 00:05.800
So let's solve another SQL interview question.

4
00:06.840 --> 00:11.000
What is the difference between row number rank and dense rank functions?

5
00:12.280 --> 00:19.120
That is a category of ranking functions in SQL server or any of the SQL RDBMS.

6
00:19.800 --> 00:30.280
So the row number and rank functions and dense rank functions are used to assign numbers to rows within

7
00:30.280 --> 00:31.320
a result set.

8
00:32.360 --> 00:35.000
Number two rows within a result set.

9
00:39.360 --> 00:43.600
However, their behavior differs when dealing with duplicate values.

10
00:44.480 --> 00:51.840
Row number gives a unique sequential number to each row, even if multiple rows have the same value.

11
00:52.560 --> 00:55.000
On the other hand, rank.

12
00:55.440 --> 01:03.560
On the other hand, rank assigns the same rank to duplicate values in leaving gaps in the ranking sequence.

13
01:04.640 --> 01:05.400
And whereas.

14
01:05.400 --> 01:05.540
Is.

15
01:08.620 --> 01:10.220
Dense rank also.

16
01:12.780 --> 01:28.540
Assigns the same rank to duplicate values, but does not, leaving gaps in the ranking sequence that

17
01:28.540 --> 01:32.140
is the main difference between rank and dense rank, right?

18
01:32.180 --> 01:35.860
That is the main difference between rank and dense dense rank.

19
01:36.540 --> 01:44.740
Now this difference is crucial in applications where ties needs to be handled differently.

20
01:45.620 --> 01:48.740
Ties means what ties means duplicates, right?

21
01:50.780 --> 01:53.420
Ties means duplicates.

22
01:53.980 --> 01:59.020
Now, I have already created this table and inserted the data.

23
01:59.460 --> 02:02.220
Now I will explain you in the Excel file.

24
02:02.220 --> 02:02.660
Right.

25
02:03.580 --> 02:05.780
Now I will explain you in the Excel file.

26
02:06.140 --> 02:07.860
So this is my data, right?

27
02:07.860 --> 02:08.900
This is my data.

28
02:09.660 --> 02:19.680
Now One thing you need to consider over here is in the ranking functions like row number, rank, dense

29
02:19.680 --> 02:20.800
rank and tile.

30
02:21.680 --> 02:30.920
These clauses you will be definitely have or optionally have like over clause right over clause.

31
02:31.600 --> 02:32.240
Then what.

32
02:34.400 --> 02:35.880
Partition by clause.

33
02:37.480 --> 02:38.440
And then what?

34
02:39.280 --> 02:40.520
Order by clause.

35
02:42.240 --> 02:42.640
Right.

36
02:42.640 --> 02:46.440
These three clauses either mandatory or optional.

37
02:50.000 --> 02:51.880
In case of ranking functions.

38
02:52.360 --> 03:03.600
Now if you see partition by is almost working as a group by but it will not aggregate the data, it

39
03:03.600 --> 03:11.960
will show entire row wise data only, but it will not aggregate the data, but it will be a kind of

40
03:12.000 --> 03:13.160
a partition means.

41
03:13.560 --> 03:18.940
Suppose in this particular our data Main ID one is one partition right?

42
03:18.980 --> 03:20.820
Main ID one is one partition.

43
03:22.220 --> 03:23.540
Up to here.

44
03:23.740 --> 03:27.900
And this is my second partition.

45
03:27.900 --> 03:28.180
Right.

46
03:28.180 --> 03:32.060
This is my second partition because ID is change right.

47
03:32.780 --> 03:37.220
So now if you see over here row number what will do.

48
03:37.260 --> 03:37.860
Row number.

49
03:37.900 --> 03:42.340
Row number will generate unique sequential number.

50
03:43.860 --> 03:46.940
Row number will generate unique sequential number.

51
03:53.220 --> 03:58.180
So if you see but up to when it will generate up to the partition up.

52
03:58.220 --> 04:01.300
Because we have mentioned over here partition by clause.

53
04:01.820 --> 04:05.060
That is why up to this particular partition it will generate.

54
04:05.460 --> 04:13.020
So if you highlight this particular records with yellow, and if you highlight this particular record

55
04:14.540 --> 04:16.700
with green, then you will understand better.

56
04:16.940 --> 04:19.820
So see 12345.

57
04:21.040 --> 04:23.960
These are unique numbers for this particular partition.

58
04:23.960 --> 04:28.600
And now once the partition is changed again, it will start from one, two, three.

59
04:28.600 --> 04:31.240
Right again it will start from one, two, three.

60
04:31.960 --> 04:34.240
Now ranking how ranking will work.

61
04:34.720 --> 04:38.240
So see over here we have given order by value descending.

62
04:40.520 --> 04:45.000
Order by value descending means highest value should have rank one.

63
04:45.200 --> 04:49.960
So in this particular partition which is the highest value 13 is the highest value right.

64
04:50.360 --> 04:52.200
So it will be having rank one.

65
04:52.960 --> 04:56.560
Then this second highest value will be have rank two.

66
04:57.040 --> 05:06.200
But now see this is having rank three because both are having the same duplicate data.

67
05:06.200 --> 05:07.680
Right 12 value.

68
05:08.360 --> 05:17.720
But now after that when value will change it will not generate four in the ranking functions right?

69
05:17.760 --> 05:22.560
It will giving some gaps when the duplicate or ties are found.

70
05:23.080 --> 05:27.450
So this is my virtually.

71
05:27.450 --> 05:31.730
You have to visualize that this is my third rank, right?

72
05:32.490 --> 05:33.490
This is my third rank.

73
05:33.530 --> 05:34.770
This is my fourth rank.

74
05:34.930 --> 05:40.410
And this should be five, but not in case of dense rank.

75
05:40.450 --> 05:42.930
It will continuously generate the number.

76
05:43.730 --> 05:45.010
So three, three and four.

77
05:46.490 --> 05:47.970
Now again this one is changed.

78
05:47.970 --> 05:50.810
So see over here 123 row number.

79
05:51.410 --> 05:54.490
Unique sequential number in case of row number.

80
05:54.890 --> 05:57.330
Now over here one one and three.

81
05:57.330 --> 06:06.970
Because it will not generate the continuous number when the duplicates are found whereas dense rank

82
06:07.130 --> 06:07.690
is generated.

83
06:07.690 --> 06:08.010
Right.

84
06:08.010 --> 06:14.690
But now see our over here now is partitioned by clause mandatory.

85
06:14.690 --> 06:15.370
Let's see.

86
06:17.490 --> 06:21.170
I am trying to remove this partition by clause now.

87
06:24.450 --> 06:26.290
And try to run the query.

88
06:26.330 --> 06:28.410
What is the result I am getting?

89
06:29.210 --> 06:31.330
See now entire.

90
06:32.310 --> 06:33.790
table will be one partition.

91
06:33.830 --> 06:36.990
So 12345678.

92
06:37.670 --> 06:38.270
Now see.

93
06:38.310 --> 06:41.470
1233.

94
06:42.630 --> 06:45.790
Because it is same value over here.

95
06:45.830 --> 06:46.550
1212.

96
06:46.550 --> 06:48.750
So both the 12 it will generate three rank.

97
06:49.030 --> 06:55.270
And then it will skip one rank because there is one row only of same value.

98
06:55.270 --> 06:55.590
Right.

99
06:55.630 --> 07:00.110
Two row of only four same value that is 12.

100
07:00.110 --> 07:02.630
So it will skip one and then generate five.

101
07:03.070 --> 07:08.390
Now six six it will skip one because of this same value and it will generate eight.

102
07:09.870 --> 07:12.230
Now see the dense rank.

103
07:12.230 --> 07:14.070
What will do three three.

104
07:14.070 --> 07:15.110
It will not generate five.

105
07:15.110 --> 07:19.670
It will generate four because it is generating continuous numbers only.

106
07:19.670 --> 07:20.070
Right.

107
07:21.590 --> 07:23.430
And now five, five and six.

108
07:24.430 --> 07:29.350
So it means partition by clause is not mandatory.

109
07:29.390 --> 07:35.430
Now let me try to delete this one whether it will allowed me or not.

110
07:35.430 --> 07:36.070
Let's see.

111
07:42.250 --> 07:42.970
Okay.

112
07:42.970 --> 07:45.250
Let me try to execute whether it will.

113
07:47.450 --> 07:51.890
The function row number must have overflows with order by.

114
07:53.290 --> 08:00.930
So it means the row number function must have overflows is compulsory.

115
08:02.210 --> 08:04.370
See over clauses.

116
08:08.250 --> 08:11.730
Compulsory order by clauses.

117
08:14.690 --> 08:18.050
Compulsory and partition by clauses.

118
08:18.330 --> 08:21.410
Not mandatory.

119
08:21.410 --> 08:21.690
Right.

120
08:21.730 --> 08:23.170
It is not mandatory.

121
08:24.170 --> 08:33.290
So this is how we are using the ranking functions, which is rank, row number and rank function with

122
08:33.290 --> 08:38.370
partition or without partition by clause that you can explain in the interview.

123
08:38.370 --> 08:38.770
Right.

124
08:39.810 --> 08:41.010
So thank you.