query brings in dups that i don't want

[mcarter]

I have a query brings in all my data from different resources.
When I look at the resulting data, I see that f1 has dup records.
ID f1 f2 f3 date
1 1 6 6 2/11/2008
2 1 7 7 4/02/2008
3 2 5 5 2/11/2008
4 3 4 4 2/11/2008

Is there any way to keep from extracting the duplicate records in my
original query that gathers all my data? ie, i just want to bring
in the record with the latest date if f1 is a dup.

Or do I have to query on the original query to clean up dups?

 

[Marshall Barton]

I have a query brings in all my data from different resources.
When I look at the resulting data, I see that f1 has dup records.
ID f1 f2 f3 date
1 1 6 6 2/11/2008
2 1 7 7 4/02/2008
3 2 5 5 2/11/2008
4 3 4 4 2/11/2008

Is there any way to keep from extracting the duplicate records in my
original query that gathers all my data? ie, i just want to bring
in the record with the latest date if f1 is a dup.

I think it will require two queries, but they can be
combined into one:

SELECT table.ID, table.f1, table.f2, table.f3, table.date
FROM table
INNER JOIN (SELECT X.f1, Max(X.date) As Latest
FROM table As X
GROUP BY X.f1) As L
ON table.f1 = L.f1 And table.date = L.Latest