sqlsql-serverselectduplicates

Give first duplicate a 1 and the rest 0

I have data which contains 1000+ lines and in this it contains errors people make. I have added a extra column and would like to find all duplicate Rev Names and give the first one a 1 and all remaining duplicates a 0. When there is no duplicate, it should be a 1. The outcome should look like this:

RevName   ErrorCount    Duplicate 
Rev5588   23            1
Rev5588   67            0
Rev5588   7             0
Rev5588   45            0
Rev7895   6             1
Rev9065   4             1
Rev5588   1             1

I have tried CASE WHEN but its not giving the first one a 1, its giving them all zeros.

Solution

  • You could use a case expression over the row_number window function:

    SELECT RevName,
       Duplicate,
       CASE ROW_NUMER() OVER (PARTITION BY RevName
                              ORDER BY (SELECT 1)) WHEN 1 THEN 1 ELSE 0 END AS Duplicate
FROM   mytable