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
- 4243 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
How to display top 10 rows in mysql?
What is prepared statement in mysql?
What is dirty read and phantom read?
How to create table show example
Where’s database data actually stored?
What is data types in mysql?
What are the advantages of mysql?
How would you get the current date in mysql?
Can you tell which of the following where clauses is faster?
Why do you think it is advised to not to use guid and character columns as clustered index arrays?
How does mysql store data?
What's the difference between mysql and sql server?
what is database white box testing? : Mysql dba
Why we use mongodb instead of mysql?
How do I rename a mysql database?
