Eliminating duplicates by pulling rows with max date

Transact-SQL
1

Hi,

I have been joined one table to pull one extra column for existing query., But after I added that table results were getting duploicates. When I checked on it found that there is multiple rows for few Items but we need to pull only the date inserted recently which means it has column "RowUpdatedateTime" , need to get max of
this date.... So I am not sure how can I pull this...

This is the way I am joining my table called ItemFaciVendors

LEFT OUTER JOIN ItemFaciVendors IFV
ON IIS.ItemNumber = IFV.CatalogNumber

and I am pulling IFV.VendorNumber in select list...

But I need to restrict duplicates by getting only IFV.RowUpdatedateTime is higher date.

Can you please suggest how can I achieve this?

Thank you..

2

Please show the entire query

3

Change this to:

OUTER APPLY (SELECT TOP 1 * FROM ItemFaciVendors v WHERE v.CatalogNumber = IIS.ItemNumber ORDER BY RowUpdatedateTime desc) AS IFV

This assumes there are no other requirements for the join that is causing the duplicate values. I would assume that there is some indicator in the table ItemFaciVendors that indicates which row is the current row - but if the only indicator is the update date then the above will work.

4

Thank you it helped.... :slight_smile: