IF OBJECT_ID('tempdb..#SiteMatches') IS NOT NULL

DROP TABLE #SiteMatches

CREATE TABLE #SiteMatches -- the SOURCE

(ID BIGINT IDENTITY(1,1) NOT NULL,

RetainedRID BIGINT NOT NULL,

SiteID BIGINT NOT NULL,

Country VARCHAR(30)

PRIMARY KEY(ID))

INSERT INTO #SiteMatches

SELECT 137478, 1, 'UK' UNION ALL

SELECT 87728738, 1, 'UK' UNION ALL

SELECT 95615, 1, 'UK' UNION ALL

SELECT 851566, 2, 'SCOTLAND' UNION ALL

SELECT 447422, 2, 'SCOTLAND' UNION ALL

SELECT 188827, 2, 'SCOTLAND' UNION ALL

SELECT 326887, 2, 'SCOTLAND'

IF OBJECT_ID('tempdb..#SiteMatches_Output') IS NOT NULL

DROP TABLE #SiteMatches_Output

CREATE TABLE #SiteMatches_Output -- the DESTINATION

(ID BIGINT IDENTITY(1,1) NOT NULL,

RetainedRID BIGINT NOT NULL,

SiteID BIGINT NOT NULL,

Country VARCHAR(100),

PRIMARY KEY(ID))

INSERT INTO #SiteMatches_Output

SELECT 137478, 1, 'UK' UNION ALL

SELECT 87728738, 1, 'UK' UNION ALL

SELECT 95615, 1, 'UK' UNION ALL

SELECT 851566, 2, 'SCOTLAND' UNION ALL

SELECT 447422, 2, 'SCOTLAND' UNION ALL

SELECT 188827, 2, 'SCOTLAND' UNION ALL

SELECT 326887, 2, 'SCOTLAND'

/* GROUPED BY*/

--variation 1

SELECT

ROW_NUMBER() OVER (PARTITION BY S1.ID ORDER BY S1.SiteID) AS RowNum

,S1.ID

,S1.RetainedRID

,S1.SiteID

,S1.CountryFROM

,#SiteMatches_Output S1

--variation 2

SELECT

ROW_NUMBER() OVER (PARTITION BY S2.RetainedRID ORDER BY S2.SiteID) AS RowNum

,S2.ID

,S2.RetainedRID

,S2.SiteID

,S2.Country

FROM

#SiteMatches_Output S2

--variation 3

SELECT

ROW_NUMBER() OVER (PARTITION BY S3.Country ORDER BY S3.SiteID DESC) AS RowNum

,S3.ID

,S3.RetainedRID

,S3.SiteID

,S3.Country

FROM

#SiteMatches_Output S3