1 00:00.840 --> 00:01.800 Hello, everyone. 2 00:02.840 --> 00:04.680 So welcome back to my channel. 3 00:05.840 --> 00:14.200 Uh, last time we have seen the SQL query, which is showing the third highest salary, right, third 4 00:14.200 --> 00:19.360 highest salary in the third highest salary of a particular employee. 5 00:19.360 --> 00:21.280 But it is department wise. 6 00:21.920 --> 00:25.200 So let me take this data into the Excel file. 7 00:30.760 --> 00:33.040 So this is our data. 8 00:37.760 --> 00:40.720 And we have uh few departments are there. 9 00:40.720 --> 00:41.160 Right. 10 00:41.880 --> 00:49.440 We have few departments are there which is either mechanical education and income tax. 11 00:49.920 --> 00:54.120 Right now how we can say the third highest salary. 12 00:54.760 --> 00:55.280 So. 13 00:59.080 --> 01:02.900 So we have solved this query using a partition by clause. 14 01:02.900 --> 01:10.260 Write using a partition by clause and denserank function, and then the partition by. 15 01:10.260 --> 01:16.700 We have taken the department id write d.id in order by salary descending. 16 01:17.300 --> 01:23.900 So let me directly copy this solution result and then. 17 01:28.980 --> 01:33.940 So if you see over here for IT department where is the IT department. 18 01:35.100 --> 01:36.500 It is a fourth one right. 19 01:39.580 --> 01:41.140 This is a IT department. 20 01:41.140 --> 01:45.820 And in the IT department which is the third highest salary. 21 01:45.980 --> 01:46.420 So. 22 01:48.860 --> 01:50.180 This is the first one. 23 01:50.180 --> 01:51.540 This is the second one. 24 01:54.580 --> 01:55.780 And this is the third one. 25 01:55.780 --> 01:56.220 Right. 26 02:01.170 --> 02:04.530 Actually this is department ID for which is education department. 27 02:04.530 --> 02:05.010 Right. 28 02:05.610 --> 02:11.130 So, uh, this is the third highest salary, right, for this education department. 29 02:11.130 --> 02:13.890 And how we can say that this is education department. 30 02:14.290 --> 02:16.130 So for this, uh. 31 02:18.530 --> 02:21.810 Department table, you can see over here, right. 32 02:23.290 --> 02:32.290 And this is the third is 76,000, then 65,000, and then 64,000 means it is the third is salary. 33 02:32.530 --> 02:33.930 And which is showing over here. 34 02:33.930 --> 02:34.410 Right. 35 02:35.170 --> 02:36.930 In the education department. 36 02:37.650 --> 02:39.650 But now our requirement is. 37 02:42.170 --> 02:48.930 We require the top three highest salary, top three highest salary across all departments. 38 02:48.930 --> 02:50.410 So how can we get that? 39 02:51.770 --> 02:58.770 Again let me uh, go to the select statements and then order by. 40 03:00.710 --> 03:02.910 salary descending. 41 03:02.910 --> 03:06.630 Let me take the descending. 42 03:06.670 --> 03:07.350 And then. 43 03:14.150 --> 03:19.990 Here we can do department ID also, right, so that we can find the perfect result. 44 03:20.470 --> 03:21.190 Okay. 45 03:22.750 --> 03:23.990 Department ID. 46 03:31.710 --> 03:32.950 Department ID one. 47 03:32.950 --> 03:40.230 If you want department ID one first, then let me remove this thing. 48 03:41.230 --> 03:49.310 So first it will do sorting based upon the department ID and then salary descending. 49 03:49.310 --> 03:54.030 Now let me try to execute this query okay. 50 03:54.430 --> 03:56.870 So now it is giving a perfect result right. 51 03:56.910 --> 03:58.510 What we require. 52 03:59.100 --> 04:04.700 Now let me again this let copy this data into the Excel file over here. 53 04:08.340 --> 04:12.820 Now here you will easily get the third is salary right. 54 04:12.940 --> 04:15.980 So this is the third highest salary in department ID one. 55 04:17.820 --> 04:20.060 Then uh, for department ID two. 56 04:22.100 --> 04:23.420 One second and third. 57 04:23.420 --> 04:26.580 This is the third highest salary in Department ID. 58 04:28.740 --> 04:29.140 Two. 59 04:29.180 --> 04:34.580 So which is already which is also showing over here right in this result set. 60 04:36.900 --> 04:40.260 But now our requirement is we require. 61 04:44.700 --> 04:54.780 We require the top three highest salary across all departments means we require three highest salary. 62 04:54.820 --> 04:56.660 Not the third highest salary only. 63 04:56.660 --> 04:57.140 Right. 64 04:57.800 --> 05:01.200 So let me remove this dense ring function. 65 05:01.320 --> 05:02.680 Rank function as of now. 66 05:03.320 --> 05:04.360 Now for that. 67 05:04.760 --> 05:09.320 Can anyone tell me what is the changes we have to do over here? 68 05:10.280 --> 05:12.080 Because it is showing over here. 69 05:12.080 --> 05:14.160 Only third eye salary. 70 05:14.640 --> 05:17.720 But now we require up to here, right? 71 05:20.320 --> 05:23.280 Let me copy the highlight. 72 05:23.280 --> 05:24.360 This result set. 73 05:24.960 --> 05:26.080 What is required. 74 05:26.840 --> 05:27.360 So. 75 05:29.720 --> 05:33.160 In this we require this one for department ID two. 76 05:33.440 --> 05:34.640 Then department ID three. 77 05:34.680 --> 05:38.160 What is our requirement for department ID three. 78 05:38.400 --> 05:39.880 This is our requirement. 79 05:43.760 --> 05:45.880 Then department ID four. 80 05:50.880 --> 05:52.480 This is our requirement. 81 05:53.640 --> 05:55.160 Then Department ID five. 82 06:00.420 --> 06:01.900 This is our requirement. 83 06:02.700 --> 06:07.620 So 1516 rows should be required right? 84 06:07.980 --> 06:10.540 Because of this 80,000 is coming twice. 85 06:10.540 --> 06:13.820 So it is uh giving both the result. 86 06:13.860 --> 06:14.380 Right. 87 06:15.340 --> 06:20.260 So now this is our requirement, not this one. 88 06:20.620 --> 06:22.220 What we have done over here. 89 06:27.820 --> 06:32.060 So now our requirement is this one we require top three. 90 06:37.260 --> 06:40.220 Highest salary across. 91 06:42.580 --> 06:46.420 All the departments right. 92 06:49.380 --> 06:51.740 So for that purpose what we can do. 93 07:02.050 --> 07:03.370 Oh my God. 94 07:07.890 --> 07:08.210 Okay. 95 07:08.210 --> 07:08.930 One minute. 96 07:19.410 --> 07:19.890 Okay. 97 07:20.890 --> 07:27.810 So for that purpose, what we can do over here, let me jump to the SQL server. 98 07:36.650 --> 07:42.010 So let me uh, this query is giving the third highest salary, right. 99 07:42.290 --> 07:44.250 This query is giving the third highest salary. 100 07:44.290 --> 07:48.570 Now if we run this query. 101 07:55.610 --> 08:00.990 And so here we require one, two and three. 102 08:01.350 --> 08:04.150 So one and two also we require right. 103 08:04.190 --> 08:05.950 Rank one and two is also required. 104 08:05.950 --> 08:08.750 And one rank three also is already. 105 08:09.430 --> 08:13.390 Currently we are retrieving rank three because we require third highest salary. 106 08:13.390 --> 08:15.390 But now rank one and two. 107 08:15.390 --> 08:16.310 Also we require. 108 08:16.310 --> 08:21.350 So it is very simple in this query what which we have done in the last lecture. 109 08:21.870 --> 08:24.470 We need to do less than or equal to three. 110 08:26.070 --> 08:29.830 And now let me execute this query. 111 08:30.710 --> 08:32.710 It should give 16 rows right. 112 08:33.230 --> 08:39.470 Because all the department we have three departments four five departments it air mechanical education 113 08:39.630 --> 08:40.710 and income tax. 114 08:40.870 --> 08:49.030 So five multiplied by 315 plus one because this one is giving 80,000 is coming two times. 115 08:49.030 --> 08:49.270 Right. 116 08:49.270 --> 08:50.950 So this should be our result set. 117 08:56.220 --> 09:03.660 Top three highest salary across all the departments. 118 09:03.660 --> 09:03.980 Right. 119 09:04.020 --> 09:06.300 So this should be our result set. 120 09:08.620 --> 09:12.860 So only one change we have done in place of. 121 09:16.940 --> 09:18.540 Equal to operator. 122 09:21.300 --> 09:25.580 We have done less than. 123 09:25.580 --> 09:27.100 Equal to it means. 124 09:30.980 --> 09:38.660 It will give rank one and two data as well. 125 09:38.700 --> 09:39.180 Right. 126 09:41.340 --> 09:44.860 So let me know if there is any further doubt in this query. 127 09:46.380 --> 09:53.140 And please do like if you really like this video and then comment and subscribe and share. 128 09:54.060 --> 09:54.820 Thank you.