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 cteAS (        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 valFROM cteWHERE ad = 1ORDER BY C ASCThe main problem is to improve the readability.Would be great to optimize also the performance, but it's not mandatory.
Efficient way to extract the first row in a Group By group
sql;sql server
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;