T-SQL Query(Microsoft SQL Server)

I have a SQL table

ID Value
__ _____
1   A
2   B
3   B
4   B
5   C
6   C
7   D

I want to remove the repeating values from the table, such that there are only distinct values in the table.
hence the values 3B, 4B & 6C should be deleted.

After the execution of the SQL query, The final output should be:

ID  value
__   ____
1    A
2    B
5    C
7    D

How can I accomplish this? Please help.
Thanks in advance,
vad2319Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select value, min(id) from yourtable group by value
0
 
dqmqCommented:
I'm guessing you've simplified the question and what you really want is:

Select * from YourTable T1
inner join
(Select Min(ID) from YourTable group by value) T2 on T1.ID = T2.ID
0
 
dportasCommented:
If you want to DELETE rather than just query then try:

DELETE FROM tbl
WHERE EXISTS
 (SELECT *
  FROM tbl AS t
  WHERE Value = tbl.Value
   AND id < tbl.id);

This assumes ID is unique and is untested - test it out and take a backup before you try it for real.
0
 
vad2319Author Commented:
Thanks guys for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.