>>It's the same thing - adjust the inner select (derived table) only.
>>
>>select RO.*, E.* from RO inner JOIN
>>(select top (10) Event.RO_NO, min(DateTime) as LastUpdate
>>from Event where DateTime > 'DateValue'
>>and not exists (select 1 from
>>Event E where DateTime <='DateValue' and E.Ro_No = Event.Ro_No)
>>GROUP BY Ro_NO
>>order by MIN(Event.DateTime)) E on RO.RO_NO = E.RO_NO
>
>Actually, changing MIN() to MAX() resolves the issue. I just tested it. Out of those 2002 records, they represent in fact only 56 RO. In the RO table, there are 56 records. So, the process took 6 loops (10x5) and one last one with 6 records.
>
>Thanks for the help
Are you sure it did? E.g. you do need to use MAX, of course, but who will guarantee you will not get the same RO_NO again after your robot processed 10 records?
If it's not broken, fix it until it is.
My Blog