Efficient way to extract the first row in a Group By group

I have a large Sql Server view with this schema:

• [archive_ID] (int, not null)

• [archive_date] (datetime, not null)

• [archdata_path_ID] (varchar(50), not null)

• [archdata_value] (int not null)

I need to group the records by the Date, and I need to extract just the first record for each group.

This is the current query:

WITH cte
AS (
        SELECT *
               ,CAST(archive_date AS DATE) AS C
               ,ROW_NUMBER() OVER (
                       PARTITION BY CAST(archive_date AS DATE) ORDER BY CAST(archive_date AS DATE) ASC
                       ) AS ad
        FROM ArchiveData
        WHERE archdata_path_ID = @PathID
        )
SELECT DISTINCT C
        ,archdata_value AS val
FROM cte
WHERE ad = 1
ORDER BY C ASC

The main problem is to improve the readability.
Would be great to optimize also the performance, but it's not mandatory.

I believe that DISTINCT is redundant, since the CTE should produce only one row for each date whose ROW_NUMBER() is 1.

Avoid selecting * in the CTE, and list the columns you want explicitly.

Your naming is poor: CTE, C, val, ad. Please find more descriptive names.

If you are using any SQL Server ≥ 2012, then FIRST_VALUE() is the function you want.

SELECT CAST(archive_date AS DATE) AS C
     , FIRST_VALUE(archdata_value) OVER (
           PARTITION BY CAST(archive_date AS DATE)
               ORDER BY CAST(archive_date AS DATE)
       ) AS val
    FROM ArchiveData
    WHERE archdata_path_ID = @PathID
    ORDER BY C;

StackExchange Code Review Q#59011, answer score: 4