I have Json data as below:
Declare @json varchar(max)
Set @json = N'[
{"Area":"160","GNum":"7752","ENum":"00150","GNavn":"Eid","GType":"Troms og Finnmark","GStatus":"I bruk","TypeNum":"258"},
{"Area":"1167","GNum":"496590.90","ENum":"00158","GNavn":"Eid","GType":"Vestland","GStatus":"I ABX bruk","TypeNum":"271"},
{"Area":"2438","GNum":"3876","ENum":"00061","GNavn":"Eid","GType":"Oslo","GStatus":"I Offentlig bruk","TypeNum":"80"},
{"Area":"1623","GNum":"2205","ENum":"00061","GNavn":"Eid","GType":"Oslo","GStatus":"I bruk","TypeNum":"86"},
{"Area":"348","GNum":673,"ENum":"00062","GNavn":"Eid","GType":"Oslo","GStatus":"I bruk","TypeNum":"99"},
{"Area":"374","GNum":"673","ENum":"00062","GNavn":"Eid","GType":"Oslo","GStatus":"I XYZ bruk","TypeNum":"100"},
{"Area":"1740","GNum":"1742","ENum":"00064","GNavn":"Eid","GType":"Vestland","GStatus":"I bruk","TypeNum":"100"},
{"Area":null,"GNum":"5254","ENum":"00067","GNavn":"Eid","GType":"Vestland","GStatus":"I bruk","TypeNum":"107"},
{"Area":null,"GNum":"4948","ENum":"00067","GNavn":"Festet","GType":"Vestland","GStatus":"I bruk","TypeNum":"108"},
{"Area":"3886","GNum":"7192","ENum":"00068","GNavn":"Eid","GType":"Agder","GStatus":"I 56 bruk","TypeNum":"109"},
{"Area":"1189","GNum":"1320","ENum":"00074","GNavn":"Eid","GType":"Oslo","GStatus":"I bruk","TypeNum":"141"},
{"Area":"1189","GNum":"1320","ENum":"14900","GNavn":"Eid","GType":"Oslo","GStatus":"I 123 bruk","TypeNum":"141"}
]';
In above Json, I have to check if "GNum" field has more than 1 duplicate/same record then save only last record out of all selected duplicates.
I have to implement this in Sql Stored Procedure.
I am able to get pair of duplicate records using below query:
select * from [mdcdev].[Table1] ou
where (select count(*) from [mdcdev].[Table1] inr
where inr.GNum = ou.GNum) > 1
Table 1 Structure
CREATE TABLE [mdcdev].[Table1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[GNum] [varchar](16) NOT NULL,
[GNavn] [varchar](256) NOT NULL,
[GType] [varchar](256) NOT NULL,
[GStatus] [varchar](256) NULL,
[TypeNum] [varchar](256) NULL,
[ENum] [varchar](256) NULL,
[Area] [varchar](256) NULL
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
