Query which takes out duplicate records if year is 2004 over 2003

[tubbsy123]

Hi,

I have a table which is the result of two merged tables containing 2003 and 2004 data. The merged table looks like this:

Merged:
ID Name Year
1 John 2003
2 John 2004
3 Mark 2003
4 Adam 2004

I am trying to retrieve ID, Name, Year for all records. Unless there are duplicates, ie John, then the record taken should be 2004 over 2003.

I am stuck on how to do this and it's probably a really simple query. Any help would be grateful.

Thanks a lot, regards

tubbsy123

 

[hneal98]

It could be done in a two step process. Do a query of all 2004 records. Put them in a temp table. Do another query that compares the two tables and if pull only records from the 2004 that are not already in the 2003.

Not sure if that is what you are looking for, but it works.

 

[tubbsy123]

I have tried doing that, but I couldn't get it to work. This is the SQL statement which I used:

SELECT 2003.ID, 2004.ID, 2003.Name, 2004.Name, 2003.Year, 2004.Year
FROM 2004 INNER JOIN 2003 ON 2004.ID = 2003.ID
WHERE 2003.Year <> 2004.Year;

Have you any tips on how to make this work?

Thanks a lot, Regards

Tubbsy123

 

[PHV]

SELECT A.ID, A.Name, A.Year
FROM yourTable A
WHERE A.Year = (SELECT Max(B.Year) FROM yourTable B WHERE B.Name = A.Name)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244