Posted by bbcworldtour on 12/08/06 11:11

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.lastAmendedWhen =
(
select max(lastAmendedWhen)
from patel p2
where p2.indexID = p1.indexID
and datepart(year,p2.lastAmendedWhen) =
datepart(year,p1.lastAmendedWhen)
and datepart(month,p2.lastAmendedWhen) =
datepart(month,p1.lastAmendedWhen)
and datepart(day,p2.lastAmendedWhen) =
datepart(day,p1.lastAmendedWhen)
)

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

Best regards

Bo Brunsgaard

[Back to original message]