Return to Answer

Allow me to suggest using the ROW_NUMBER function, which allows you to number returned rows using the ordering you provide.

Here is a complete query that uses the ROW_NUMBER function. I also removed the SELECT * and replaced them with the column names.

SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM 
(
 SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
 FROM ArchiveData
) A
WHERE A.Row = 1

You could also use a CTE or temp table to store the results of the inner query. I would recommend using a temp table for the reasons outlined in this DBA.SE question

Here is an example using a temp table:

CREATE TABLE #ArchivedData
(
 ID INT PRIMARY KEY,
 PathKey VARCHAR(50) NOT NULL,
 InsertDate DATETIME NOT NULL,
 Value INT NOT NULL,
 Row INT NOT NULL
)
INSERT INTO #ArchivedData
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
FROM ArchiveData
 
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM #ArchivedData
WHERE Row = 1
DROP TABLE #ArchivedData

Allow me to suggest using the ROW_NUMBER function, which allows you to number returned rows using the ordering you provide.

Here is a complete query that uses the ROW_NUMBER function. I also removed the SELECT * and replaced them with the column names.

SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM 
(
 SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
 FROM ArchiveData
) A
WHERE A.Row = 1

You could also use a CTE or temp table to store the results of the inner query. I would recommend using a temp table for the reasons outlined in this DBA.SE question

Here is an example using a temp table:

CREATE TABLE #ArchivedData
(
 ID INT PRIMARY KEY,
 PathKey VARCHAR(50) NOT NULL,
 InsertDate DATETIME NOT NULL,
 Value INT NOT NULL,
 Row INT NOT NULL
)
INSERT INTO #ArchivedData
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
FROM ArchiveData
 
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM #ArchivedData
WHERE Row = 1
DROP TABLE #ArchivedData

Allow me to suggest using the ROW_NUMBER function.

Here is a complete query that uses the ROW_NUMBER function. I also removed the SELECT * and replaced them with the column names.

SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM 
(
 SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
 FROM ArchiveData
) A
WHERE A.Row = 1

You could also use a CTE or temp table to store the results of the inner query. I would recommend using a temp table for the reasons outlined in this DBA.SE question

Here is an example using a temp table:

CREATE TABLE #ArchivedData
(
 ID INT PRIMARY KEY,
 PathKey VARCHAR(50) NOT NULL,
 InsertDate DATETIME NOT NULL,
 Value INT NOT NULL,
 Row INT NOT NULL
)
INSERT INTO #ArchivedData
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
FROM ArchiveData
 
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM #ArchivedData
WHERE Row = 1
DROP TABLE #ArchivedData

Allow me to suggest using the ROW_NUMBER function, which allows you to number returned rows using the ordering you provide.

Here is a complete query that uses the ROW_NUMBER function. I also removed the SELECT * and replaced them with the column names.

SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM 
(
 SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
 FROM ArchiveData
) A
WHERE A.Row = 1

You could also use a CTE or temp table to store the results of the inner query. I would recommend using a temp table for the reasons outlined in this DBA.SE question

Here is an example using a temp table:

CREATE TABLE #ArchivedData
(
 ID INT PRIMARY KEY,
 PathKey VARCHAR(50) NOT NULL,
 InsertDate DATETIME NOT NULL,
 Value INT NOT NULL,
 Row INT NOT NULL
)
INSERT INTO #ArchivedData
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value,
 ROW_NUMBER() OVER (PARTITION BY PathKey ORDER BY ID DESC) AS Row
FROM ArchiveData
 
SELECT 
 ID,
 PathKey, 
 InsertDate, 
 Value
FROM #ArchivedData
WHERE Row = 1
DROP TABLE #ArchivedData