3

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would be the select to get all rows from this table, but only select 1 of the duplicates at a time? I have around 100k duplicates, and my DB guy is still on christmas vacation :|

the tables do have unique keys, but other than the keys the data is duplicated a few thousand times. I'd like to avoid deleting the data (just in case). Server is running MSSQL2012, and i'll edit this post with a table structure when I get back to my desk.

10

You can use a CTE for this, if you want the row that's returned to be a complete, intact row rather than aggregates of any of the other columns. You can change the ORDER BY to prefer rows by any of the columns (the grouping is by the ones you think should be unique).

;WITH x AS
(
  SELECT col1, col2, col3, 
    rn = ROW_NUMBER() OVER 
    (
        PARTITION BY unique_columns 
        ORDER BY unique_columns, tie_breaker_if_you_care
    )
  FROM dbo.source_table
)
SELECT col1, col2, col3 FROM x WHERE rn = 1;
2

Jaco's suggestion of using DISTINCT will work in a lot of cases.

If you need information about fields that may vary between the "duplicates" then you can instead group the identical items by and use aggregates on the rest:

SELECT field1, field2
     , COUNT(*) AS NumberOfCopies
     , MIN(some_numeric_or_date_field_that_varies) AS minValue
     , MAX(some_numeric_or_date_field_that_varies) AS maxValue
GROUP BY field1, field2

If you need specific information about the first or last duplicates then you could try using the window functions (particularly ROW_NUMBER()) but that gets a little more complicated. If you can add some more detail about the data to the question we can give more concrete examples of this if it seems relevant.

1

You can use DISTINCT, but make sure you omit any columns that can vary for two duplicate rows, for example timestamps:

SELECT DISTINCT Field1, Field2
FROM dbo.Table

Or alternatively you can group by the fields that are the part of the natural key, for example an order id or invoice number:

SELECT order_id, 
       MAX(total_order_value) AS total_order_value, 
       MAX(number_of_items) AS number_of_items, 
       MAX(price_per_item) AS price_per_item,
FROM dbo.Table
GROUP BY order_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.