From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Lonni J Friedman'" <netllama(at)gmail(dot)com>, "'pgsql-general'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: updating rows which have a common value forconsecutive dates |
Date: | 2011-04-13 16:57:20 |
Message-ID: | 00b101cbf9fb$daa9cde0$8ffd69a0$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
If you have the ability to use Window functions you can group (as necessary), order by last_update, and then use rank() to number 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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)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 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!
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-04-13 16:58:23 | Re: Weird WAL problem - 9.0.3 |
Previous Message | Tom Lane | 2011-04-13 16:50:34 | Re: Cursor metadata |