Re: Help with query

Christian Hofmann <christian(dot)hofmann(at)gmx(dot)de> schrieb:
> But my tables are normalized. In the normal table there is only one state
> for a given project_no.
> So your queries would success.
> But the table we are talking about is a auditing table. It it logging all
> events that are going to the normal table.
> So it saves when a row is inserted, updated or deleted.

Okay.

> When in the normal table the name for the project_no 1 is altered there will
> be also only one row. But in the auditing table (the one I am talking about)
> there will be one row for every update, insert or delete in the normal
> table.

Okay. Perhaps there are other solutions: you can create a new table
(project_no, project_name), with a primary index on project_no.
And create a trigger on the original table to insert or update this
table. Then you can join this table to other querys.

Perhaps there are other solutions with tricky joins to select the latest
project_name for every project_no and joining this to your select.

test=# select * from pname;
id | name
----+----------
1 | TESTxyz
2 | TEST2xyz
(2 rows)

test=# select pnr, pname.name, max(ts) from p left join pname on
pnr=pname.id where ts < '2006-01-15 17:04:00' group by pnr, pname.name;
pnr | name | max
-----+----------+-------------------------
1 | TESTxyz | 2006-01-15 17:03:37.937
2 | TEST2xyz | 2006-01-15 17:03:37.937
(2 rows)

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°