Cannot retrieve latest entry for each year.

Hello

I am trying to retrieve the latest (most recent) entry for each year and I am stumped. I write MySQL but unfamiliar with SQL. Here is my failed attempt so far:

Select VictoryID,Title,Campaign,body,Image,Year,max(LastE dited) From Victory where Year IN (Select distinct(Year) FROM Victory)

I have tried a zillion variations and am willing to completely rewrite this select statement.

I want like all the info about the returned result essentially but want one for each year.

Where an I going wrong here?

Hi,
If you were using SQL2005, the below query would help you.

But I think you are developing on MySQL


WITH CTE(Year)
AS
(
SELECT DISTINCT(DATEPART(YY,InsertedDate)) FROM Sales(NoLock)
)
SELECT * FROM
(
SELECT
MAX(InsertedDate) InsertedDate
FROM Sales(NoLock)
INNER JOIN CTE ON CTE.Year = DATEPART(YY,InsertedDate)
GROUP BY CTE.Year
) CTE_OUT
INNER JOIN Sales (NoLock) ON Sales.InsertedDate = CTE_OUT.InsertedDate


Eralper
http://www.kodyaz.com