SQL Query. Duplicate Records in Data

[loz75]

Hi,
I have collected data from SQL Query where there are duplicate records. The PO Number is the same, as is the Vendor Number, however, the date is different.

I want SQL Query to look at these records, and pick the one with the most present date. I assume I need some sort of IF statement in the query, however, cannot get my head around how to do this - can anyone help?

Laura

 

[JamesLean]

So you have a table that looks something like this?:

PONum | VendNum | Dt
1 | 1 | 01/01/2003
1 | 1 | 05/02/2003
2 | 3 | 02/04/2003
2 | 3 | 04/04/2003

And you just want to return a single row for each combination of PONum/VendNum? This query will return the row which has the latest date:

SELECT ponum, vendnum, dt
FROM mytable t1
WHERE dt = (SELECT MAX(dt) FROM mytable WHERE ponum = t1.ponum AND vendnum = t1.vendnum)

I hope my assumtions are correct. If not can you clarify what you need?

--James

 

[loz75]

Thanks James. I'm going to give it a go what you have suggested - I'll let you know.
Thanks.
Laura

 

[quatermass]

Laura,

You can set the sql statement to pick up the latest date, by using maximum and group
Example using Northwind table, Orders:

SELECT EmployeeID, MAX(ShippedDate) AS Expr1
FROM Orders
GROUP BY EmployeeID

This will return the employee id with the latest shipped date for that employee id.


What you will need to do for your situation is to have all fields in the select statement (excpet for the date), repeated in the group by clause.

That will then give you all of your 'duplicated' fields as a single record with the latest date.


Hope it helps

Kevin

**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************