Re: updating rows which have a common value forconsecutive dates

If you have the ability to use Window functions you can group (as necessary), order by last_update, and then use rank()
tonumber each test run sequentially.  Then you can limit the results to  ( rank() <= 2 AND current_status = 'FAILED' ). 

David J.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Lonni J Friedman
Sent: Wednesday, April 13, 2011 3:34 AM
To: pgsql-general
Subject: [GENERAL] updating rows which have a common value forconsecutive dates

Greetings,
I have a table full of automated test data, which continuously has new unique data inserted:

    Column     |            Type             |
 Modifiers
----------------+-----------------------------+-------------------------
----------------+-----------------------------+-------------------------
----------------+-----------------------------+-----------
 id             | integer                     | not null default
nextval('dbltests_id_seq'::regclass)
 testname       | text                        | not null
 last_update    | timestamp without time zone | not null default now()
 current_status | text                        | not null
 os             | text                        | not null
 arch           | text                        | not null
 build_type     | text                        | not null
 branch         | text                        | not null

The 'testname' column contains many different tests, and each unique 'testname' has numerous different associated
os,arch,build_type& branch values.  For example, testname='foo' will run on
os='Linux',arch='i686',build_type='debug',branch='t38',and also on
os='Windows7',arch='x86_64',build_type='release',branch='r40',etc, and there will be many other different testname's
withsimilar permutations of the os,arch,build_type & branch columns.  So for example, there will also be testname='bar'
ortestname='omega' or testname='sigma' for the other assorted os,arch,build_type & branch permutations. 

The current_status column is either 'PASSED' or 'FAILED'.

What I'm struggling with is how to put together a SQL query which shows me all instances of a given testname
permutation
(os,arch,build_type,branch) which has a current_status value of 'FAILED' for two or more most recent consecutive
'last_update'values. 

Suggestions welcome.

thanks in advance!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general