Return single row per year from a data table
I need to show only single row from each year based on the following data.
AlbumID AlbumIcon Year
2 53575dd7-9e6a-4230-afe7-eb34a9952ef5.jpg 2010
6 41c6cefe-aa9a-40af-86bf-0e78784fc9be.jpg 2011
7 20472dc8-abff-4cb1-80a4-ec264a91025e.jpg 2011
9 a2185c73-debf-4058-afee-dc54caf674d2.jpg 2012
10 3e3de287-b966-40cc-8895-9c9e98f1b23d.jpg 2012
11 f0743e3e-26d8-44ea-97ad-a7bd33259550.JPG 2007
12 dafd1242-19d6-4f66-8b23-75143f107743.JPG 2012
13 d623e134-3d69-4b86-86fe-4c5a2ab89086.JPG 2012
14 9c6b981e-e696-4c85-be6d-3b04559a6cd4.JPG 2012
15 b6b867c0-5478-4503-940c-ce75e863bee8.JPG 2012
16 3cc0d762-f1e1-425a-8923-0e39b4ed0eca.jpg 2012
my Table structure is like this
Album_ID int
Album_Name varchar(100)
Album_Description varchar(500)
Album_Date Datetime
Album_Icon varchar(60)
I am using SELECTAlbum_ID, Album_Name,Album_Icon,
DATEPART(YYYY,Album_Date) AS Year FROM Album_Name I am not sure how to
modify the query to just get single row from each year and the latest year
row would be great.
I want to achieve this without using CTE query
Group by with DISTINCT generates errors
SELECT Album_ID, Album_Name,Album_Icon,
DISTINCT(DATEPART(YYYY,Album_Date)) AS Year FROM Album_Name GROUP BY
DATEPART(YYYY,Album_Date) , Album_ID, Album_Icon
Help in this regarding is appreciated.
Solution Based on Bagdan reply.
SELECT *
FROM
(
SELECT x.*,
--ROW_NUMBER() OVER(PARTITION BY x.[Album_Date] ORDER BY
x.Album_ID DESC) AS RowNum
ROW_NUMBER() OVER(PARTITION BY DATEPART(YYYY,x.[Album_Date])
ORDER BY x.Album_Date DESC) AS RowNum
FROM Album_Name x
) src
WHERE src.RowNum = 1
No comments:
Post a Comment