Skip site navigation (1) Skip section navigation (2)

Help with query

From: "Christian Hofmann" <christian(dot)hofmann(at)gmx(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Help with query
Date: 2006-01-15 18:19:04
Message-ID: 00b301c61a00$2ae4aeb0$9000a8c0@taschenrechner (view raw or flat)
Thread:
Lists: pgsql-novice
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



Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2006-01-15 18:33:48
Subject: Re: help with query: advanced ORDER BY...
Previous:From: Michael GlaesemannDate: 2006-01-15 10:59:24
Subject: Re: help with query: advanced ORDER BY...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group