1 00:00.920 --> 00:02.000 Hello everyone. 2 00:02.520 --> 00:03.520 Good morning all. 3 00:04.640 --> 00:06.280 Welcome back to my channel. 4 00:07.120 --> 00:09.760 So let's solve another SQL problem. 5 00:11.200 --> 00:13.680 So they've given us the customers table. 6 00:14.280 --> 00:16.840 Orders table and products table. 7 00:17.840 --> 00:23.240 So in the customers table customer ID and name column is given. 8 00:23.800 --> 00:27.760 Customer ID is the column with the unique values for this table. 9 00:29.600 --> 00:36.160 And then in the orders table order ID order date customer ID and product ID is given. 10 00:37.760 --> 00:40.800 Order ID is column with unique values for this table. 11 00:40.840 --> 00:46.000 This table contains information about orders made by each customer. 12 00:48.040 --> 00:53.280 There will be no product ordered by same user more than once in one day. 13 00:54.000 --> 00:58.640 And in the products table, product ID, product name and price column is given. 14 00:59.600 --> 01:07.680 So in the orders table, customer ID column is just look like a foreign key in the orders table, which 15 01:07.680 --> 01:14.560 is primary key in the customers table in same as product ID is the foreign key in the orders table, 16 01:14.560 --> 01:17.840 which is primary key in the products table. 17 01:18.560 --> 01:28.000 Now what is our requirement right solution right solution to find the most recent orders of each product, 18 01:29.240 --> 01:36.760 return the result table order by product name in ascending order, and in case of any tie by the product 19 01:36.760 --> 01:40.000 ID in ascending order. 20 01:40.680 --> 01:45.520 If there is still a tie, order them by order ID in ascending order. 21 01:45.520 --> 01:47.960 The result format is in the following example. 22 01:48.760 --> 01:51.280 Now this is the sample customers table. 23 01:52.080 --> 01:55.200 This is orders table and products table. 24 01:56.120 --> 01:57.600 Now what is our requirement. 25 01:57.600 --> 02:04.800 We need to find most recent transaction made by each product right. 26 02:06.560 --> 02:15.360 So if you see over here in the customers table, customer ID one is their right and their same customer 27 02:15.360 --> 02:18.040 ID will be there in orders table. 28 02:18.040 --> 02:23.720 So we need to identify where there is a one in the customer ID column in orders table. 29 02:23.720 --> 02:31.200 So one is over here which is 31st July, then one is over here which is 10th of June, then one is over 30 02:31.200 --> 02:33.920 here, 3rd of August. 31 02:36.000 --> 02:36.440 Sorry. 32 02:36.480 --> 02:38.760 We need to look into the product ID right. 33 02:39.200 --> 02:46.560 Because we are identifying by product ID so one is over here which is 1st of August and one is over 34 02:46.560 --> 02:48.920 here which is 1st of August. 35 02:49.560 --> 02:52.760 So these two will be our requirement. 36 02:52.920 --> 02:54.520 This one first requirement. 37 02:55.040 --> 02:55.840 First output. 38 02:55.840 --> 02:57.680 This is the second result set. 39 02:58.360 --> 03:04.480 Then we need to look for product ID customer ID equal to two. 40 03:05.520 --> 03:07.840 And over here customer ID equal to two. 41 03:07.880 --> 03:09.440 Where where it is coming. 42 03:09.440 --> 03:09.880 So. 43 03:12.230 --> 03:18.790 Two is coming over here, then two is coming over here and two is coming over here. 44 03:24.270 --> 03:24.710 So. 45 03:28.150 --> 03:32.430 This two has the highest number of order date, right. 46 03:33.710 --> 03:35.150 Which is 3rd of August. 47 03:35.590 --> 03:38.030 So this should be our result set. 48 03:39.390 --> 03:42.910 This is our third third row in the result set. 49 03:43.310 --> 03:43.830 Now. 50 03:46.790 --> 03:49.630 We need to look for the product ID three. 51 03:50.750 --> 03:58.590 So product ID three is coming one time over here two time second time over here. 52 03:58.910 --> 04:00.430 So which is the highest date? 53 04:00.590 --> 04:04.830 It is 29th of August that we have to pick only right. 54 04:04.990 --> 04:09.910 So this is our fourth row in the result set. 55 04:10.550 --> 04:15.510 So this four row we require and then we need to do order by. 56 04:15.550 --> 04:19.030 So I have created the table and inserted the data into the same. 57 04:20.190 --> 04:27.470 Now I will put this create table in insert statement script in the description box of the video. 58 04:28.350 --> 04:31.990 Now let's see how we can solve it in the SQL query. 59 04:32.630 --> 04:38.550 So we just need to do use the rank function and partition by product ID or product name. 60 04:39.110 --> 04:40.550 Anything we can do. 61 04:42.710 --> 04:43.110 Right. 62 04:43.110 --> 04:44.030 Anything we can do. 63 04:44.070 --> 04:53.350 And then order by order date descending because we want highest date records only. 64 04:54.550 --> 05:00.830 So it will give rank to highest date as first rank, then second highest date for any particular product 65 05:00.870 --> 05:03.830 name will give the second rank like that. 66 05:04.630 --> 05:14.990 Then we need to definitely join all the three tables and then select the required column from the all 67 05:15.030 --> 05:15.750 three tables. 68 05:15.750 --> 05:17.150 Then do rank function. 69 05:17.350 --> 05:18.390 Rank function. 70 05:18.870 --> 05:20.910 And then we cannot. 71 05:21.230 --> 05:24.550 We want only the rank equal to one record, right. 72 05:27.230 --> 05:29.670 We want only rank equal to one record. 73 05:30.310 --> 05:36.030 So one, two, three and four which we have seen right. 74 05:36.950 --> 05:39.070 So now we cannot write that. 75 05:39.110 --> 05:39.550 Where. 76 05:40.070 --> 05:42.550 Over here that where rank equal to one. 77 05:43.150 --> 05:46.950 So I have to wrap up this code into the city. 78 05:46.950 --> 05:55.990 And then outside city I have just mentioned from city where k equal to one and then order by product 79 05:56.030 --> 05:57.390 name and order id. 80 05:58.070 --> 06:00.230 So this should be our result set. 81 06:04.070 --> 06:09.550 So this is what we have mentioned in the explanation right. 82 06:10.710 --> 06:13.110 So thank you so much everyone. 83 06:13.910 --> 06:17.830 We will meet again with the next SQL question as soon as possible. 84 06:18.430 --> 06:19.190 Thank you.