Re: Help with query - Mailing list pgsql-novice

Christian Hofmann <christian.hofmann@gmx.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°