T-SQL Query(Microsoft SQL Server)

Posted on 2007-08-07
Last Modified: 2008-01-09
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,
Question by:vad2319
    LVL 142

    Accepted Solution

    select value, min(id) from yourtable group by value
    LVL 42

    Expert Comment

    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
    LVL 22

    Expert Comment

    If you want to DELETE rather than just query then try:

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

    This assumes ID is unique and is untested - test it out and take a backup before you try it for real.

    Author Comment

    Thanks guys for your help.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now