Hi David, Thanks for your reply. I'm using 8.4.7, so window functions are certainly an option, although I've admittedly never used them before. I've spent the past few hours reading the dox, and I now have a rudimentary understanding of window functions. I tried to compose a query based on your suggestion, but I think i'm running up against my lack of experience. This query seems to give me all failures but not neccesarily when there are two in a row for a unique group (although I'm still not 100% certain its actually returning only last_update consecutive rows): SELECT testname,os,arch,build_type,branch,current_status,last_update,rank() OVER (PARTITION BY testname,os,arch,branch,build_type ORDER BY last_update DESC) FROM mytable WHERE current_status='FAILED' AND age(now(),last_update) <= INTERVAL '15 days'
However, if I'm understanding how this works, what I really care about is when a rank=2 exists, as that's truly when something failed for two consecutive last_update's. I thought this might do it, but apparently I'm doing it wrong: SELECT testname,os,arch,build_type,branch,current_status,last_update,rank() OVER (PARTITION BY testname,os,arch,branch,build_type ORDER BY last_update DESC) FROM mytable WHERE current_status='FAILED' AND age(now(),last_update) <= INTERVAL '15 days' AND rank()=2 ; ERROR: window function call requires an OVER clause LINE 1: ... age(now(),last_update) <= INTERVAL '15 days' AND rank()=2 ; How do I restrict the results to only show when rank=2 ? thanks! On Wed, Apr 13, 2011 at 9:57 AM, David Johnston <pol...@yahoo.com> wrote: > 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-ow...@postgresql.org > [mailto:pgsql-general-ow...@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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
