Searching for Last DateTime

[patelk23@gmail.com]

Hi everyone,
I am really struggling to do the following in sql. Your expertise will
be very welcome.

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.

[bbcworldtour@hotmail.com]

Re: Searching for Last DateTime

patelk23@gmail. com skrev:

>
I want to pick out rows with the lastAmendedWhen for each dates. So my
output should look like

You should be able to do it using a correleated sub-query. Something
along the lines of:

select indexID
, indexVersion
, lastAmendedWhen
from patel p1
where p1.lastAmendedW hen =
(
select max(lastAmended When)
from patel p2
where p2.indexID = p1.indexID
and datepart(year,p 2.lastAmendedWh en) =
datepart(year,p 1.lastAmendedWh en)
and datepart(month, p2.lastAmendedW hen) =
datepart(month, p1.lastAmendedW hen)
and datepart(day,p2 .lastAmendedWhe n) =
datepart(day,p1 .lastAmendedWhe n)
)

There may be more efficient ways of doing it, but this ought to work as
a starting point.

Best regards

Bo Brunsgaard

[Erland Sommarskog]

Re: Searching for Last DateTime

(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.LastAmendedWh en
FROM TestData a
JOIN (SELECT IndexID, LastAmendedWhen = MAX(LastAmended When)
FROM TestData
GROUP BY IndexID,
convert(char(8) , LastAmendedWhen , 112)) AS b
ON a.IndexID = b.IndexID
AND a.LastAmendedWh en = b.LastAmendedWh en

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@sommarsk og.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