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.