CODEWITHSUNDEEP
sqlsql-serverselectsql-server-2012
Kaptah
Kaptah

Reputation: 9891

SELECT and create new ID for unique ids having identical values on 2 columns

My table

 ID  Name    Addr   tDate
-------------------------------
| 1 | Aa  |  street |  20151231
| 2 | Aa  |  street |  20130202
| 2 | Aa  |  street |  20120101
| 3 | Aa  |  way    |  20150821
| 4 | Bb  |  street |  20150821 
| 7 | Xb  |  street |  20150821 
| 5 | Cc  |  way    |  20150821
| 5 | Cc  |  way    |  20150821
| 6 | Cc  |  no way |  20150821

Result 01 

 ID  Name    Addr   | tDate
-------------------------------
| 1 | Aa  |  street |  20151231
| 2 | Aa  |  street |  20130202
| 2 | Aa  |  street |  20120101

Going to create a new nID It should be copied as is OR merged if Name and Addr are identical and selecting ID with newest tDate

Result 02

 ID  Name    Addr      tDate      nID
------------------------------------
| 1 | Aa  |  street |  20151231 | 1
| 2 | Aa  |  street |  20120101 | 1    <-- nID != ID
| 2 | Aa  |  street |  20130202 | 1    <-- nID != ID
| 3 | Aa  |  way    |  20150821 | 3
| 4 | Bb  |  street |  20150821 | 4 
| 7 | Xb  |  street |  20150821 | 7 
| 5 | Cc  |  way    |  20150821 | 5
| 5 | Cc  |  way    |  20150821 | 5
| 6 | Cc  |  no way |  20150821 | 6

I've tried this. Not sure if it's correct. 

   SELECT DISTINCT dr.*
                FROM MyTable dr
                inner join(
                    SELECT ID, Name, Addr
                    FROM MyTable
                    GROUP BY ID, Name, Addr
                ) ss on dr.Name = ss.Name and 
                        dr.Addr = ss.Addr and 
                        dr.ID <> ss.ID 
                order by Name

Upvotes: 4

Views: 451

Answers (3)

MatBailie
MatBailie

Reputation: 86735

EDIT : Complete change after your addition of tDate, and need for two result sets

Result Set 1:

SELECT
    id, Name, Addr, tDate
FROM
(
    SELECT
        *,
        COUNT(*) OVER (PARTITION BY Name, Addr)   AS occurrences
    FROM
        MyTable
)
    AS parsed
WHERE
   occurrences > 1

Result Set 2:

SELECT
    *,
    FIRST_VALUE(ID) OVER (PARTITION BY Name, Addr
                              ORDER BY tDate DESC
                               ROWS UNBOUNDED PRECEDING)   AS nID
FROM
    MyTable
ORDER BY
    ID

Example : http://sqlfiddle.com/#!6/9285ae/9

Upvotes: 5

Madhivanan
Madhivanan

Reputation: 13700

You should use RANK without partition

SELECT   ID, Name, Addr,
         RANK() OVER (ORDER BY Id,name,addr) AS nID
FROM     table

Upvotes: -1

Mureinik
Mureinik

Reputation: 311478

The dense_rank window function should do the trick:

SELECT   ID, Name, Addr
         DENSE_RANK() OVER (PARTITION BY Name, Addr ORDER BY ID) AS nID
FROM     mytable
ORDER BY 1, 4

Upvotes: 1

Related Questions