CODEWITHSUNDEEP
sql-servert-sqlduplicates
disasterkid
disasterkid

Reputation: 7278

Ignore duplicates and not deleting them

I already know how to delete duplicate rows on an Id column. But I am not allowed to delete anything on the server. What I need is a WITH statement that ignores duplicate rows (takes only one of them). Is there a way to do this without modifying data in a table?

P.S.1 All the duplicates rows are identical. So there is no need to decide which one to keep.

P.S.2 I'd rather not create an extra temp table (SELECT * INTO ...)

Upvotes: 1

Views: 46

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

You can use a CTE which does a select DISTINCT on all columns:

WITH cte AS (
    SELECT DISTINCT Id, col1, col2, ..., colN
    FROM yourTable
)

You could also achieve this using GROUP BY on all columns:

WITH cte AS (
    SELECT Id, col1, col2, ..., colN
    FROM yourTable
    GROUP BY Id, col1, col2, ..., colN
)

If the Id values are not duplicated, but all other columns are, then you can try:

WITH cte AS (
    SELECT MIN(Id) AS Id, col1, col2, ..., colN
    FROM yourTable
    GROUP BY col1, col2, ..., colN
)

Upvotes: 1

Related Questions