vad2319
asked on
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,
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,
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks guys for your help.
Select * from YourTable T1
inner join
(Select Min(ID) from YourTable group by value) T2 on T1.ID = T2.ID