Select statement using dates 1

[tmunson99]

Does someone have any suggestions on how to accomplish the following. I know a stored procedure is one option, but I'm hoping to do it in a view so I need a sql query.

I have thousands of records with one or more records per project id. I want to only retrieve two records per id based on the two most recent timestamps (or one record if there is only one).

id timestamp

1 2006-07-09 13:00:00
1 2006-07-01 09:30:00
2 2006-07-03 10:15:00
2 2006-06-30 12:18:00
3 2006-06-18 04:00:00
3 2006-06-01 08:13:00
4 2006-07-25 14:35:00
4 2006-07-24 12:35:00

Thanks!

 

[DotNetGnat]

Select A.ID, A.timestamp
from mytable A
WHERE A.timestamp in
       (
        Select Top 2 timestamp from
        mytable B where A.ID=B.ID
        Order by timestamp DESC
       )

-DNG

 

[tmunson99]

That worked! Thank you!