how to select rows by id and date?

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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
 
John said:
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
Click to expand...
Why wouldn't

4 2008 A

be included?
 
Back
Top