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
