updating rows which have a common value forconsecutive dates

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: updating rows which have a common value forconsecutive dates
Date: 2011-04-13 07:33:37
Message-ID: BANLkTimesQKTEo-Nrp4aRJLb1XbeXe_fEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 with similar
permutations of the os,arch,build_type & branch columns. So for
example, there will also be testname='bar' or testname='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!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Henry C. 2011-04-13 08:18:27 Re: Trying out replication: cp cannot stat log file during recovery
Previous Message Jeremy Palmer 2011-04-13 07:30:09 Re: 9.0 Out of memory