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
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 append query?
What are the security alerts while using mysql?
How does mysql store data?
how to search second maximum(second highest) salary value(integer)from table employee (field salary)in the manner so that mysql gets less load?
What does do in mysql?
Is mysql a scripting language?
What is mysql server used for?
What are the differences between binary and varbinary?
What is striped backup
How do I make an action query?
Is mysql a backend?
How to do login in mysql with unix shell.
How to Set a root password if there is on root password.
How do I find the size of a mysql database?
What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?