Paul Stevens Paul Stevens - 2 months ago 6
SQL Question

Select statement to return an updated table with duplicates on three columns filtered out

What select statement would return an updated table from the following table where the rows which are duplicates on all three columns have been filtered out.

ID Date Username
2368 2012-01-31 00:00:00 asmith
2368 2012-08-23 00:00:00 asmith
3151 2012-01-05 00:00:00 asmith
3151 2012-01-11 00:00:00 asmith
3151 2012-01-11 00:00:00 asmith
3171 2012-02-01 00:00:00 asmith
3412 2012-03-27 00:00:00 asmith
3420 2012-03-19 00:00:00 asmith
3423 2012-07-12 00:00:00 asmith
3431 2012-04-04 00:00:00 asmith
3460 2012-03-10 00:00:00 asmith
3519 2013-12-17 00:00:00 asmith
3539 2012-05-21 00:00:00 asmith
3541 2012-07-03 00:00:00 asmith
3572 2012-03-22 00:00:00 asmith
3572 2012-03-24 00:00:00 asmith
3572 2012-03-25 00:00:00 asmith
3572 2012-03-25 00:00:00 asmith


So after the duplicates for ID values 3151 and 3572 have been removed the resulting table will look like this.

ID Date Username
2368 2012-01-31 00:00:00 asmith
2368 2012-08-23 00:00:00 asmith
3151 2012-01-05 00:00:00 asmith
3171 2012-02-01 00:00:00 asmith
3412 2012-03-27 00:00:00 asmith
3420 2012-03-19 00:00:00 asmith
3423 2012-07-12 00:00:00 asmith
3431 2012-04-04 00:00:00 asmith
3460 2012-03-10 00:00:00 asmith
3519 2013-12-17 00:00:00 asmith
3539 2012-05-21 00:00:00 asmith
3541 2012-07-03 00:00:00 asmith
3572 2012-03-22 00:00:00 asmith
3572 2012-03-24 00:00:00 asmith

Answer

USE Row_NUMBER

;WITH CTE AS (
  select id, date, username,ROW_NUMBER() OVER (PARTITION BY id,Date ORDER BY ID) RN 
  from TABLE
)
SELECT * FROM CTE WHERE RN=1

Alternative way to do this

select id, date, username
from TABLE
GROUP BY id, date, username
HAVING COUNT(*) = 1
Comments