how to delete distinct record from a table in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hi,suppose i have a table struture with data as

SQL

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 7-Aug-12 9:41am

software_Engi08

Updated 7-Aug-12 9:45am

Kenneth Haugland

v2

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Philip Stuyck · Answer 1 · 2012-08-07T09:54:00

Solution 1

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;

Posted 7-Aug-12 9:54am

Philip Stuyck

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.

Santhosh Kumar Jayaraman · Answer 2 · 2012-08-07T15:33:00

Solution 2

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;

Posted 7-Aug-12 15:33pm

Santhosh Kumar Jayaraman

Comments

Vasim889 8-Aug-12 1:13am

he want solution for sql server 2005.?pls read qustion?