>Can you help me with the derived table. What is it exactly do I need to do to fix this?
In your derived table remove OrdNo and other unique fields and group by the rest of the fields and get max of your possible dup field. Then join with the main table based on all fields you grouped on.
In other words,
select myTable.* from myTable inner join (select myGrpField1, myGrpField2, ;
max(Field3) as MaxField from myTable group by 1,2 where myCondition) myDerived ;
ON myTable.myGrpField1 = myDerived.myGrpField1 and ;
myTable.myGrpField2 = myDerived.myGrpField2 ;
and myTable.Field3 = myDerived.MaxField
There are other ways to get the same info.
Let me know if you need help verbatim with your original query or the info above is enough.
If it's not broken, fix it until it is.
My Blog