Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 65226B5DD2A for ; Thu, 14 Jul 2011 08:33:29 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 50226-02 for ; Thu, 14 Jul 2011 11:31:41 +0000 (UTC) Received: from mx1.hub.org (mx1.hub.org [200.46.208.106]) by mail.postgresql.org (Postfix) with ESMTP id ECF8EB5D91F for ; Thu, 14 Jul 2011 08:31:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mx1.hub.org (Postfix) with ESMTP id AD50227FBBAD for ; Thu, 14 Jul 2011 08:31:30 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id N7D9441W; Thu, 14 Jul 2011 12:30:40 +0100 Message-ID: <58E9388858734E8E8988EBD9E49E678F@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: "LALIT KUMAR" Cc: References: <275BDBE63A0449DABCAF052D8EC34AC5@marktestcr.marktest.pt> Subject: Re: [NOVICE] Compare rows Date: Thu, 14 Jul 2011 12:30:40 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002C_01CC4221.D7631290" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, NO_DNS_FOR_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201107/75 X-Sequence-Number: 35645 This is a multi-part message in MIME format. ------=_NextPart_000_002C_01CC4221.D7631290 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ----- Original Message -----=20 From: LALIT KUMAR=20 To: Oliveiros d'Azevedo Cristina=20 Hi, I do have order of rows. The problem originally is: I need to set all = flag with value 0 to -1 whose next row has flag as 1. This is to be = done for each city separetly, i.e. flag of last row of city A need not = be changed due to first row of city B. City Date Flag =20 A 31-01-1991 0 =20 A 03-03-1991 1 =20 A 04-04-1991 0 A 06-08-1991 0 A 13-01-1992 1 A 12-03-1992 1 B 12-01-1988 0 B 19-03-1999 1 B 01-04-1999 0 B 09-06-2001 1 The dates for a villages are in sorted order.Expected output: City Date = Flag =20 A 03-03-1991 1 = =20 A 31-01-1991 -1 = =20 A 04-04-1991 0 A 06-08-1991 -1=20 A 13-01-1992 1 A 12-03-1992 1 B 12-01-1988 = -1 B 19-03-1999 1 B 01-04-1999 = -1 B 09-06-2001 = 1 =20 The (city,Date) is unique for each tuple.Hope I am able to state the = problem correctly. Thanks Lalit * Hi, Lalit,=20 First, please always CC to mailing list. You may get help faster from = someone with more "savoir faire" than me. Second, Try this out and tell me if it produced the output you intended UPDATE yourTable SET "Flag" =3D -1 FROM ( SELECT x."Date" as "Date",x."City" as "City",x."Flag" as "Flag" = ,MAX(y."Date") as anterior FROM yourTable x JOIN yourTable y ON y."City" =3D x."City" AND y."Date" < x."Date"=20 GROUP BY x."Date",x."City",x."Flag" ) umq WHERE yourTable."City" =3D umq."City" and anterior =3D = yourTable."Date" AND yourTable."Flag" =3D 0 AND umq."Flag" =3D 1 HTH, Best, Oliver ------=_NextPart_000_002C_01CC4221.D7631290 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 
----- Original Message -----
From:=20 LALIT KUMAR=20
To: Oliveiros d'Azevedo = Cristina=20
 

Hi,

I do have order of rows. The problem originally is: I need to set = all=20 flag with value 0 to -1 whose next row has flag as 1. This  is to = be done=20 for each city separetly, i.e. flag of last row of city A need not be = changed=20 due to first row of city B.

City          Date       =  =20          Flag         =  =20             
A         31-01-1991       =  =20       0            
A         03-03-1991       =  =20       1     
A         04-04-1991       =  =20       0
A         06-08-1991       =  =20       0
A        13-01-1992       =  =20        1
A       =  12-03-1992  =20              1
B     =  =20  12-01-1988              =20  0
B        19-03-1999       =  =20       1
B        01-04-1999       =  =20        0
B        09-06-2001       =  =20         1

The dates for a villages are in sorted order.Expected = output:


              City   =  =20                     =  =20  Date                 =  Flag=20                    =20   
 =20               A     =    =20                03-03-1991 =  =20             1     =  
                A   =  =20                  =20  31-01-1991               -1 =  =20    
                A   =  =20                  =20  04-04-1991               = 0
                A   =  =20                    =20 06-08-1991               = -1 
                A   =  =20                    =20 13-01-1992               =  1
 =20               A     =    =20                 =  12-03-1992=20                1
  =  =20             B       =    =20                12-01-1988 =  =20              -1
                B   =  =20                    =20  19-03-1999               = 1
                B   =  =20                     =  =20 01-04-1999               =  -1
                B   =  =20                     =  =20  09-06-2001               =  =20 1
              =   
The (city,Date) is unique for each tuple.Hope I am able to state = the=20 problem correctly.
Thanks Lalit

 
* Hi, Lalit,
 
First, please always CC to mailing = list. You may=20 get help faster from someone with more "savoir faire" than=20 me.
 
Second,
 
Try this out and tell me if it = produced the=20 output you intended
 

UPDATE yourTable
SET "Flag" =3D = -1
FROM
(
SELECT  x."Date" as "Date",x."City" as = "City",x."Flag"=20 as "Flag" ,MAX(y."Date")  as anterior
FROM yourTable x
JOIN = yourTable y
ON y."City" =3D x."City"
AND y."Date" < x."Date" =
GROUP=20 BY x."Date",x."City",x."Flag"
) umq
WHERE yourTable."City" =3D = umq."City"=20 and anterior =3D yourTable."Date"
AND yourTable."Flag" =3D 0
AND = umq."Flag"=20 =3D 1
 
 
HTH,
 
Best,
Oliver
------=_NextPart_000_002C_01CC4221.D7631290--