 Hello friends, welcome to SS Unitech. My name is Susheel Singh and this is continuation of SQL Server interview questions and answers. So today I am going to discuss how we can delete duplicate records. When duplicate values are based on multiple columns. In many articles you have seen how we can delete duplicates. But on these articles you have seen the duplicate records are based on a single column. But here we have totally different scenario. Here we have duplicates but these duplicates are based on more than one columns. So let's move to another slide where we will discuss about the question. So this is our slide as you can see we have a input table and it contains three columns. First is from location then to location and then the distance between these two locations. As you can see in our first row we have Delhi to our form location and then to location is Mumbai. So distance between Delhi to Mumbai is 1415 so it has been displayed here. So again we have from location is Mumbai and to location is Delhi. So the distance between Mumbai to Delhi is 1415 as you have seen distance between Delhi to Mumbai is 1415. So these two records treated as a duplicate Delhi to Mumbai and then Mumbai to Delhi. So we want to delete one record between these two records and we want to display only one record in our output. As you can see I have deleted Mumbai to Delhi record in our output and I want to display only Delhi to Mumbai in our output and the rest of the records are distinct records. So we want to display as it is in our output. So how we can delete such type of duplicates. So let us move to SQL Server Management Studio where we will understand how we can write the query and get the output. So I am using SQL 2014 you may have another versions but the query would be same for all the versions. So I have already typed the query I want to execute. So table has been created successfully and then I want to insert some records. Record has been inserted 5 rows affected and then I am going to check the record. So here we can see we have from location and then to location and distance between these two. So here we can see we have 5 rows and in our slide we have 5 rows. So this metadata and this metadata both are same. So now how we can delete these duplicates records first is Delhi to Mumbai and then Mumbai to Delhi. So I want to delete one record between these two duplicates. So first of all if we have these duplicates values in our single column then we can use ranking function and then we can delete the duplicate. So how we can calculate and put these values in a single column. So first of all I am going to write the query select from then the table name and this time I want to select all the rows so select start from table name. So I am going to use a case statement case when the value of this from column is greater than two column then I want to merge between from column to two column. So at this time if the value of this from column is more than two column then I am going to concatenate between from column to two column. So this time execute and we can see Mumbai is greater than Delhi. So it's Mumbai Delhi Mumbai is greater than two Mexico then Mumbai Mexico and here Vietnam Thailand and here we can put one more condition if this value is not satisfied then what we want to display. So we want to concatenate between two column to from column so this is our from column. Now I am going to execute. So here we can see we have Mumbai Delhi and we have Mumbai Delhi. So these two values treated as a duplicate records and it's come a single row. So we can simply put a rank function and we can delete the duplicate. So this time I am going to use as a column and now I am going to put this value in a drive table. So this is our drive table and I am going to put as a alias them a and here I am going to write the selective statement select from this a and here I want all the rows and I am going to use row number for the ranking. So row number over and then partition by so I am going to put a partition by on the behalf of this newly created column and order by on the behalf of this column and as a row number. So here we can see so we have one then two then one one one. So here we can use a city or we can use one more drive table. So I am going to use one more drive table and name is B and then select from this table and where this row number value is equals to one and now I want to execute and we can see we have four rows Delhi to Mumbai, Mumbai to Mexico, Delhi to US and with time to Thailand. So in our slide we can see we have Delhi to Mumbai, Delhi to US, Mumbai to Mexico and with time to Thailand. So all the four rows had been presented here. By using this query we can simply calculate the output as we want and we can remove our duplicate values. So I hope you have understand how we can achieve this output by using Chico server query. So thank you so much for watching this video. If you like this video please subscribe our channel and press the bell icon so you will get all the notifications.