checking...
update: Yes, there can be, but do not need the extra records, ie. do not want those extra records.
>So there cannot be 2 records with NULL in RunSrv_TimeAtSrv for the same stutrip_stu_autoid and TripAsgn_PickUp combination?
>
>>1. tab1.stutrip_stu_autoid = tab2.stutrip_stu_autoid
>>2. TripAsgn_PickUp = 1
>>3. tab2.RunSrv_TimeAtSrv > tab1.RunSrv_TimeAtSrv
>>
>>TripAsgn_PickUp can only be a 1 or a 0, and it will not be Null.
>>
>>
>>>I see only 2 columns in your query joins
>>>LEFT JOIN
>>>(SELECT distinct * FROM #bptest where TripAsgn_PickUp = 1) tab2
>>> ON tab1.stutrip_stu_autoid = tab2.stutrip_stu_autoid AND tab2.RunSrv_TimeAtSrv > tab1.RunSrv_TimeAtSrv
>>>
>>>
>>>>no, I am using id, field2 and time in my query.
>>>>>You're using id and time only in your query.