Posted by Erland Sommarskog on 12/08/06 23:17

(patelk23@gmail.com) writes:
> I have a table [TestData] below
>
>
> IndexId IndexVersion LastAmendedWhen
> ---------- --------------- -------------------
> 29 1 06/10/2006 17:40:43
> 29 2 06/10/2006 17:41:29
> 29 3 09/10/2006 12:19:33
> 29 4 09/10/2006 12:20:03
> 29 5 10/10/2006 10:53:32
>
> I want to pick out rows with the lastAmendedWhen for each dates. So my
> output should look like
> IndexId IndexVersion LastAmendedWhen
> ---------- --------------- -------------------
> 29 2 06/10/2006 17:41:29
> 29 4 09/10/2006 12:20:03
> 29 5 10/10/2006 10:53:32
>
> Please can someone help. I am really struggling with this.

SELECT a.IndexID, a.IndexVersion, a.LastAmendedWhen
FROM TestData a
JOIN (SELECT IndexID, LastAmendedWhen = MAX(LastAmendedWhen)
FROM TestData
GROUP BY IndexID,
convert(char(8), LastAmendedWhen, 112)) AS b
ON a.IndexID = b.IndexID
AND a.LastAmendedWhen = b.LastAmendedWhen

It was not clear to me, if you want the result per IndexId, or just
by date. The query above is per index and date.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]