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
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / supriya
You want to delete the rows permanently or what
Is This Answer Correct ? | 0 Yes | 1 No |
What is int unsigned?
What are features of mysql?
what if you really want to store the timestamp data, such as the publication date of the article?
Which mysql function is used to concatenate string?
Is mysql is a programming language?
Why does mysql have so many connections?
How to save images in MySQL?
Explain the difference between delete and truncate.
What is ibdata1?
can you tell the order of sql select statement? : Mysql dba
Can foreign key have duplicate values?
How can you take the backup and restore a mysql database using php?