try this if your compatibility level is below 2005:SELECT t.*FROM tabMediaTransaction tinner join ( select ref, max(transactionDate) maxtdate from tabMediaTransaction group by ref ) d on d.ref = t.ref and d.maxtdate = t.TransactionDate
This could work too - see which performs better:select *FROM tabMediaTransaction twhere exists ( select 1 from tabMediaTransaction where ref = t.ref having max(transactiondate) = t.transactionDate )
Be One with the OptimizerTG