Re: Help with query

Christian Hofmann <christian(dot)hofmann(at)gmx(dot)de> schrieb:

> 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).

Why is project_name for a given project_no different? I think, you have
a wrong data-model.

I have created a similar table and a test-case:

test=# \d p
Table "public.p"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | integer |
op | character(1) |
pnr | integer |
ts | timestamp without time zone |

test=# select * from p;
id | op | pnr | ts
----+----+-----+-------------------------
1 | I | 1 | 2006-01-15 16:58:41.218
2 | I | 2 | 2006-01-15 16:59:16.703
3 | U | 1 | 2006-01-15 17:03:37.937
4 | U | 2 | 2006-01-15 17:03:37.937
5 | D | 1 | 2006-01-15 17:04:09.234
6 | D | 2 | 2006-01-15 17:04:09.234
(6 rows)

test=# select pnr, max(ts) from p where ts < '2006-01-15 17:04:00' group by pnr;
pnr | max
-----+-------------------------
2 | 2006-01-15 17:03:37.937
1 | 2006-01-15 17:03:37.937
(2 rows)

>
> 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.

select pnr, max(ts) from p where ts < '2006-01-15 17:06:00' and pnr not
in (select pnr from p where op = 'D' and ts < '2006-01-15 17:06:00')
group by pnr;

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°