Using this code it will select both records, e.g. if you have two records with the same min OrderNo both records would be selected.
We did mention it in our blog and I also mentioned this fact in my unpublished blog as well.
>I was wondering, if there are two rows/records that have the same value in ORDERNO field/column, how does SQL Server select one in the expression MIN(ORDERNO)? Is it random?
>
>
>>Try
>>
>>TEXT TO cSqlCommand NOSHOW
>>select C1.*, C2.LASTNAME, C2.FIRSTNAME, C2.TELEPHONE1
>> from COMPANY C1
>> LEFT JOIN (SELECT LASTNAME, FIRSTNAME, TELEPHONE1, COMP_PK FROM PEOPLE p3
>> WHERE ORDERNO = (SELECT MIN(ORDERNO) FROM PEOPLE p4 where p3.comp_pk =p4.comp_pk )) C2 ON C1.COMP_PK = C2.COMP_PK
>>ENDTEXT
>>
>>
If it's not broken, fix it until it is.
My Blog