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
5207 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

what is a cursor? : Mysql dba

990

How do I kill a mysql query?

890

What are the critical issues you have resolved in your company

2217

How do I start mysql client?

950

How do you rename a table?

966

How to update a root password.

937

What does "i_am_a_dummy flag" do in mysql?

944

What is longblob?

962

How do I make an action query?

906

Why do we use mysql?

916

How do I install the latest mysql on ubuntu?

969

What is the data source name for mysql?

928

Explain the difference between procedure and function in mysql?

976

List some comparisons operators used in mysql?

974

Is mysql better than oracle?

913