 buttons 1 I would request you please like this video, subscribe our channel, let's get started with today's video. So here if you can see we are having one of the input data. So inside the input data we are having this table like from location to location and distance. So if we can see like the Kanpur the first row from Kanpur to Unaw the distance is 18 and from Unaw to Kanpur distance is 18. So we are having the redundant rows like these two rows should not be repeated. It should be having a distinct rows. So the requirement is how we can write a sequel query to remove the redundant duplicate rows. The complexity level of this question is medium. So in the output if you can notice then we can see Kanpur to Unaw or Unaw to Kanpur it should be coming only once. So here in the output we can see it is coming from Kanpur to Unaw and distance is 18. So such type of duplicate rows we just want to remove from the table. So here the first two rows are duplicate then the Lucknow to Ayodhya and Ayodhya to Lucknow is also duplicate. So this row should be gone after that Agra to Delhi and Delhi to Agra. So like these three rows are redundant so we are required to remove these rows. So how we can write the query for getting this output. You can pause this video and you can think how we can write the query and what logic we can implement for getting the output. So let's get started. So here first thing we are required to check like from location and to location. Let's focus on only these two columns. So if we can check if the from location value is greater than the Unaw. Maybe Kanpur here we are having so Kanpur is greater than Unaw. Then we just want to concatenate Kanpur to Unaw maybe from location to to location. If we are having reverse case like in the second row on those scenarios it will jump into the else part and on that we will be going to concatenate to location with from location. So requirement we just want to create one more column that column could be flagged and that column could be having the proper order like from location to to location. So here like Kanpur to Unaw and Unaw to Kanpur. So on that column we just want to have Kanpur to Unaw and Kanpur to Unaw. Like both the rows should be having the same output. So let's jump to SSMS and we will try to implement this in practical. Now let me try to execute this script for creating the table and inserting the data into this table. So here if we can go and try to write the select ASTIC from this table then we should be able to see the same data that we have seen in the slide. Now here we just want to create one more column that column could be flagged. First we are required to check your from location value if that value is greater than the to location. Then we just want to concatenate from location with to location. Otherwise to location concatenate with from location. So for this we can simply write the case statement case when this from location is less than with to location then we just want to concatenate your from location with to location. To location like this. If we are going to concatenate and after that otherwise if this value will not be satisfied like this condition then we just want to concatenate to location with from location. At last we can put this as end and let me call this column as flag. So this is the only tricky part for creating this flag column. If you can notice here then like first two rows we are having Kanpur Unnau and Kanpur Unnau like both are coming in the same rows. So like from location is less than to location. So the first scenario like the from location is less than to location then it is going to concatenate from location with to location. In the second scenario it is not going to satisfied like from location is greater than the to location. On that scenario it will jump into the else part and will try to concatenate from there. So this is the only tricky part you have to do. Next we can simply put this into one of the CT and let me call this CT as CT and outside this here we are required to create one row number from this dataset and that row number will help us to identify like how many duplicate rows are here. So here I am going to select all the columns and next we are going to use the row number and then we can go with over clause and here we are required to do the partition by. So the partition should be happened on this newly created column that is flag and we can also do the order by on the same column maybe that is flag and let me call this as rw and if you can check what will be the output of this then we can notice like in row number if we are having duplicate then it is going to increase number by one. So like agra to deli and deli to agra we are having same values. So here it is going to row number one and row number two. Similarly for ayodhya to lakhnau and lakhnau to ayodhya it is also having duplicate so it is coming like one, two. Similarly for kanpur to now and not to kanpur. Now we can also put this into one more CT maybe CT two and after that here we can simply select CT two and here let me try to filter out where the row number value is greater than once. So it is indicating your duplicate rows will be coming. So here we can see we are having only three duplicate rows. So how we can delete here we can simply perform the delete operation. So this delete operation will be going to delete the data into your actual table. Now if we can focus on this table we can simply select ashtick from this table. So it should be having only seven rows. Earlier it was having total ten rows. So here we can see we have only seven rows and the duplicate rows has gone. Only distinct rows we are having. So I hope guys you have understood how we can write the query. So thank you so much for watching this video. Please like this video, do subscribe our channel. See you in the next video.