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
- 4268 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
What is dbms in mysql?
what is constraints?
How to returns the columns and column information pertaining to the designated table.
Where is the myisam table stored?
How can I insert images into a Mysql database?
What is mysql optimization?
How to insert dynamic values in mysql using php?
Why is the basic difference between left join, right join and inner join?
How many columns can you create for an index?
How can I connect mysql database?
How do I kill a mysql connection?
What is data type for image in mysql?
When to use order by in delete statement?
Is mysql a scripting language?
How do I setup mysql?
