how to select rows by id and date?

[John]

I have a table that lists rows of effective dated codes against IDs as such:

ID DATE CODE
1 2008 A
1 2007 B
2 2007 A
3 2007 C
4 2008 B
4 2008 A

I want a query that will return this:

A is the latest date for ID
or
A is the only row for ID

So in my example I would only return rows 1 and 2

1 2008 A
2 2007 A

Thanks

 

[Rui]

Try this statement in a query, replacing

by your table name

SELECT ID, Max([DATE]) AS Dates, CODE
FROM

GROUP BY ID, CODE
HAVING ID=1

 

[Bob Barrows [MVP]]

I have a table that lists rows of effective dated codes against IDs
as such:

ID DATE CODE
1 2008 A
1 2007 B
2 2007 A
3 2007 C
4 2008 B
4 2008 A

I want a query that will return this:

A is the latest date for ID
or
A is the only row for ID

So in my example I would only return rows 1 and 2

1 2008 A
2 2007 A

Why wouldn't

4 2008 A

be included?