Re: Help with query

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Help with query
Date: 2006-01-16 16:46:08
Message-ID: 20060116164608.GA2891@kaufbach.delug.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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°

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message operationsengineer1 2006-01-16 19:26:25 Query problem
Previous Message Ghiz x 2006-01-16 13:04:44 block transactions in stored procedures