CREATE TABLE #TBLD (NAME VARCHAR(20),NAME2 VARCHAR(20)) INSERT INTO #TBLD

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

Question Posted / bramhendra kumar

2 Answers
5250 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

Please Help Members By Posting Answers For Below Questions

How to create a test table in your mysql server?

1049

What is the difference between mysql and pdo?

985

How to analyze tables with 'mysqlcheck'?

1206

Is pdo more secure than mysqli?

883

How can I create table in mysql?

1022

What is the hostname for mysql database?

1024

How do I kill a mysql connection?

906

Explain timestamp?

972

How do I quit mysql?

935

Why we use mysql workbench?

906

How to concatenate two character strings?

1033

What is mysql shell?

986

What is relational database in mysql?

920

What is RMS Migrations

2395

What is orm in mysql?

907