Help with query

Hello, I need a little help with a query.

We have some auditing tables. Now we want to see the values that were in a
database at a special date.

Here is our table:

project_his_conno    operation    user_no    stamp    project_no
project_name
1    I    1234    2006-01-15 16:58:41.218    1    TEST
2    I    1234    2006-01-15 16:59:16.703    2    TEST2
3    U    1234    2006-01-15 17:03:37.937    1    TESTxyz
4    U    1234    2006-01-15 17:03:37.937    2    TEST2xyz
5    D    1234    2006-01-15 17:04:09.234    1    TESTxyz
6    D    1234    2006-01-15 17:04:09.234    2    TEST2xyz


I want to see the project_no and project_name at 2006-01-15 17:04:00

select project_no, project_name from p01_projects_his where
stamp<'2006-01-15 17:04:00'

But now I am getting row 1 to 4. But for every project_no I only want to get
the newest (row 3 and 4 here).

Can I use a normal query or will I have to write a function for that?

When this is solved the next part is to return nothing if the row is already
deleted. So when the query is:

select project_no, project_name from p01_projects_his where
stamp<'2006-01-15 17:06:00'

The newest rows are 5 and 6. But these rows indicate that the row was
deleted (operation = 'D') and then nothing should be returned.


I think I have do go the following way:

1. Only return the newest row for every project_no
2. If operaton = D then delete this row from the result-set.

I hope this is possible without using stored functions.

I tried to use the 'limit' at the end of the statement (for example limit
2). But this will not work, because I can not know how much rows I will
need.

How would you solve this?

Thanks,

Christian