removing duplicates in a query?

  • i know that using the DISTINCT keyword will filter out all rows where all the fields are the same but what if i want to filter out all rows where just one field is the same as in another row?

    how would I accomplish that?

  • SELECT * FROM MyTable t JOIN

    (SELECT DISTINCT DistinctRow FROM MyTable) d

    ON t.DistinctRow = d.DistinctRow

    John

  • ok i think that makes sense. now the complication is that i am already doing a join.

    my current query looks like this

    SELECT products.ID, products.Prod_Name, products.QTY, vendor.VENDOR

    FROM products, vendors

    WHERE products.vendor = vendors.vendor

    so how would i integrate your posted code into this?

  • Actually, I must have been asleep when I posted that query.  It won't work.  What we need to know is where there are duplicates in your column, which row do you want to display?.  Is it the one most recently created, the one with the most sales, or something else?

    So your query will look something like this:

    SELECT * FROM MyTable t JOIN

    (SELECT DISTINCT DistinctRow, MAX(SomeOtherCol) AS Tiebreaker FROM MyTable) d

    ON t.DistinctRow = d.DistinctRow

    AND t.SomeOtherCol = d.Tiebreaker

    If you give me the table names and column names to replace the ones I've made up, then we can look at inserting it into what you already have.

    John

  • It is painful if we have a table with complete identical rows. To delete such dumplicates, we may do as follow:

    1. Create a column (col2) with identity;

    col1     col2

    A         1

    A         2

    a         3

    B         4

    B         5

    2. Then run

    DELETE FROM Table1 WHERE col2 NOT IN (SELECT MIN(col2) FROM Table1 GROUP BY col1 HAVING COUNT(col1) > 1)

    3. Drop the column (col2);

  • run this sql query:

    select distinct * from

    now save the result returned by the query as a table and name it table1

    drop the exixting table having duplicate rows and then rename the table1 as the previous one has.

    i think this will solve ur problem

    Pankaj Tiwari

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply