Delete date values if not MAX date

[kimomakano]

I have a set of data that has two dates for each row.
The second set of dates is repetitive in some cases.
I only want the last(MAX) date retained for this second set of dates.
The raw data shows:
ID Date1 Date2
312 1/12/2013 1/18/2013
312 1/12/2013 1/29/2013
312 1/21/2013 1/29/2013
312 1/25/2013 1/29/2013
312 1/21/2013 2/20/2013
312 1/25/2013 2/20/2013

The results should be:
ID Date1 Date2
312 1/12/2013 1/18/2013
312 1/12/2013
312 1/21/2013
312 1/25/2013 1/29/2013
312 1/21/2013
312 1/25/2013 2/20/2013

Any thoughts?

[Rabbit]

Create a query that returns the max of date 1 grouping by date 2. You can then outer join that back to the table to find the ones that aren't the max.

[kimomakano]

Thanks Rabbit,
But it is not working for me, I forgot to clarify that this is only a subset of the data. There are multiple values for ID as well.
When I return the max of date1, grouped by date2 I lose some of the values for date1, I don't want that.

[Rabbit]

Then group by ID as well. You're supposed to lose some of the values for Date 1, that's the whole point, to only leave the values you want to keep. The last part of my post, where you join it back to the table is where you get the rest of the data.

[ck9663]

If it's the last date, why did 1/18 was returned being the earlier date. Why 1/21 returned blank? Why are there two dates for 1/25? The requested output does not make sense to me.


~~ CK