You need to turn the query with the window function into a sub-query and then in the outer query you can refer to the inner-query's rank() column. The inner query should effectively get you the last two test results for each context and then you can check to see if any of those failed. I have a hunch you might need a third layer of sub-queries to handle the failure aspect of the requirement properly; possibly as part of a "WITH" CTE. You for sure need to in order to. Properly utilize the rank() function limiting. Dave On Apr 14, 2011, at 0:52, Lonni J Friedman <netllama@xxxxxxxxx> wrote: > 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 <polobo@xxxxxxxxx> 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-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Lonni J Friedman >> Sent: Wednesday, April 13, 2011 3:34 AM >> To: pgsql-general >> Subject: 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
