Extract last record inserted for each key identifier

I have a table with this schema

ID, int primary key
PathKey, string not null
InsertDate, datetime not null
Value, int not null

• In the table there are a medium-large number of records. 1632 different pathkey and 645627 total records

• The PathKey is not unique (because I store also the old values).

• You can identify a record with the ID or a PathKey with the

InsertDate.

I am developing a query to extract the last record for each path key, and this is the query I am using but is ugly and incredible slow.

SELECT *
FROM ArchiveData
WHERE ID IN (
        SELECT (
                SELECT TOP 1 ID
                FROM ArchiveData
                WHERE PathKey = AD.PathKey
                ORDER BY [InsertDate] DESC
                ) AS ArchiveDataID
        FROM ArchiveData AS AD
        GROUP BY PathKey
        )
ORDER BY PathKey

Any suggestions to improve, at least, the performance?

Using a CTE (Supported by SQL Server) would help separate the logic of the query better than the sub-select in the from clause. Using the auto-increment ID is also something that can simplify the query.

Consider the following:

with MostRecent as (
    select max(ID) as ID
    from ArchiveData
    group by PathKey
)
select *
from ArchiveData inner join MostRecent on MostRecent.ID = ArchiveData.ID
order by PathKey

The above should reduce the number of joins a lot, and use a better key for the joins that are done.

StackExchange Code Review Q#64645, answer score: 2