Click here to Skip to main content
15,904,287 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,suppose i have a table struture with data as
SQL
Name id
a      1
a      1
b      1
c      1
d      2
d      2
e      2


i want result set as
SQL
b    1
c    1
e    2


Mean i want to delete the distinct record from the above with respect to both name and id ,how can i write a sql query for this.
SQL
b    1
c    1
e    2
Posted
Updated 7-Aug-12 9:45am
v2
Comments
Kenneth Haugland 7-Aug-12 15:46pm    
Whats the actual problem you are having? How to write the SQL command?
Christian Amado 7-Aug-12 15:51pm    
Fuzzy question. Improve your question, please.

SQL
WITH dups AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) AS rn
  FROM your_table
)
DELETE FROM dups WHERE rn>1;
 
Share this answer
 
Comments
Maciej Los 7-Aug-12 16:06pm    
Very good answer! +5
Santhosh Kumar Jayaraman 7-Aug-12 21:34pm    
He wants distinct record both by Name and ID
Philip Stuyck 8-Aug-12 2:11am    
It is not that clear what he want because even in his own example a 1 is missing from the result set. Anyway, there should be a key, and I think that judging from the data, name should be a key and would produce the correct result. On top of this, I am offering help, the user is allowed to think a little further too.
Vasim889 8-Aug-12 1:13am    
he want solution for sql server 2005.?pls read his qustion?
Philip Stuyck 8-Aug-12 2:08am    
this works in sql server 2005.
SQL
WITH cte_test AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Name,ID ORDER BY Name) AS rn
  FROM table1
)
DELETE FROM cte_test WHERE rn>1;
 
Share this answer
 
Comments
Vasim889 8-Aug-12 1:13am    
he want solution for sql server 2005.?pls read qustion?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900