I want to make a query where I want to eliminate the
duplicate rows from the table.
For example :
Input : Table : NAME
Column1 Column2
India USA
USA India
UK India
India UK
The desired output that I want to eliminate the duplicates
Output
India USA
UK India
Thanks
- 2 Answers
- 5425 Views
- Sun Life, I also Faced
- E-Mail Answers
Answer Posted / bramhendra kumar
CREATE TABLE #TBLD (NAME VARCHAR(20),NAME2 VARCHAR(20))
INSERT INTO #TBLD VALUES('India','USA'),('USA','India'),('UK', 'INDIA'),('India','UK')
WITH CTE
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) AS ROWNUM1,
ROW_NUMBER() OVER (PARTITION BY NAME2 ORDER BY NAME2) AS ROWNUM2
FROM #TBLD
)
DELETE FROM CTE WHERE ROWNUM1>1 OR ROWNUM2>1
SELECT * FROM #TBLD
TRUNCATE TABLE #TBLD
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
