Query to find distinct rows on the basis of Id

Question

data = [
   {id:1, type:1},
   {id:2, type:2},
   {id:1, type:3},
   {id:2, type:2}
]

table1 column(id, type)

I have to push the data in table1 such that it should have only rows with unique id, rows which are exact same can be removed by using distinct but for the rows that has same id but different type for that I want the rows whose comes last( or larger index like in example for id:1 I want it to be of type 3), I am not able to figure out this problem.

Answer 1

The input data is almost a JSON, so a possible approach is to fix the input and parse it with OPENJSON(). The result from the first OPENJSON() is a table with columns key, value and type and in case of a JSON array the key column returns the 0-based index of each item in this array.

DECLARE @data nvarchar(max) = N'[
   {id:1, type:1},
   {id:2, type:2},
   {id:1, type:3},
   {id:2, type:2}
]'

SELECT id, [type]
FROM (
   SELECT 
      j2.*,
      ROW_NUMBER() OVER (PARTITION BY j2.id ORDER BY CONVERT(int, j1.[key]) DESC) AS rn
   FROM OPENJSON(REPLACE(REPLACE(@data, 'id', '"id"'), 'type', '"type"')) j1
   CROSS APPLY OPENJSON(j1.[value]) WITH (
      id int '$.id',
      [type] int '$.type'
   ) j2
) t
WHERE rn = 1

Result:

id  type
--------
1   3
2   2