how to remove rows with duplicate values in a specific column SQL

Question

SELECT      P.CODE,
            P.STATUS,
            G.DESCRIPTION,
            x.xref_code,
            X.XREF_STATUS

FROM        table1.price_data p,
            TABLE2.GENERAL_DATA G,
            TABLE3.XREF_DATA X

WHERE        
        P.CODE = G.CODE
        AND G.CODE=X.CODE
        AND x.code=4545645
        AND X.VEHICLE_CODE=9999999

There are literally 30,000 rows to filter through...how do i alter the above query to show only unique values of x.xref_status?

Andrea Colleoni · Accepted Answer · 2012-07-16 15:14:42Z

1

It's not clear why your X.XREF_STATUS duplicates: there are dups in TABLE3 table? Or, more likely, there are many references to XREF_STATUS values in related records?

If rows differs for other columns than X.XREF_STATUS, you won't be able to pick one row instead of another to show only unique X.XREF_STATUS values.

A list of unique X.XREF_STATUS values can be obtained with:

select distinct X.XREF_STATUS
from TABLE3.XREF_DATA X
where x.code=4545645
AND X.VEHICLE_CODE=9999999

This can be joined to other tables to obtain related data, but again if you join with a table containing more than one reference to X.XREF_STATUS this will dup accordingly. You must aggregate other columns (COUNT, SUM, etc.) to avoid dups.

edited Jul 16, 2012 at 15:14

answered Jul 16, 2012 at 15:09

Andrea Colleoni

6,0613 gold badges34 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bouncingHippo Over a year ago

yes there are both dups in table3 table, and also references to xref_status values in related tables too in the query

Andrea Colleoni Over a year ago

The distinct query solves dups in table3. Multiple reference are hareder to remove because they're a more application specific issue; you can use distinct here too and try to remove from select the columns that cause the entire record to be unique.

bouncingHippo Over a year ago

youre right, multiple references are a pain...thanks Andrea, it worked for me!

Collectives™ on Stack Overflow

how to remove rows with duplicate values in a specific column SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related