[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] [NOVICE] Compare rows
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina () marktest ! pt>
Date: 2011-07-14 11:30:40
Message-ID: 58E9388858734E8E8988EBD9E49E678F () marktestcr ! marktest ! pt
[Download RAW message or body]
----- Original Message -----
From: LALIT KUMAR
To: Oliveiros d'Azevedo Cristina
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
A 31-01-1991 0
A 03-03-1991 1
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 \
A 03-03-1991 1
A 31-01-1991 -1
A 04-04-1991 0
A 06-08-1991 -1
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
The (city,Date) is unique for each tuple.Hope I am able to state the problem \
correctly. Thanks Lalit
* Hi, Lalit,
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" = -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" = x."City"
AND y."Date" < x."Date"
GROUP BY x."Date",x."City",x."Flag"
) umq
WHERE yourTable."City" = umq."City" and anterior = yourTable."Date"
AND yourTable."Flag" = 0
AND umq."Flag" = 1
HTH,
Best,
Oliver
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.5730.13" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 \
2px solid; MARGIN-RIGHT: 0px"> <DIV style="FONT: 10pt arial">----- Original Message \
----- </DIV> <DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=lalit.jss@gmail.com href="mailto:lalit.jss@gmail.com">LALIT KUMAR</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=oliveiros.cristina@marktest.pt
href="mailto:oliveiros.cristina@marktest.pt">Oliveiros d'Azevedo Cristina</A>
</DIV>
<DIV style="FONT: 10pt arial"> </DIV>
<DIV><BR></DIV><SPAN
style="FONT-SIZE: 13px; COLOR: rgb(51,51,51); FONT-FAMILY: arial, sans-serif; \
BORDER-COLLAPSE: collapse">Hi, <DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>City Date
Flag
</DIV>
<DIV>A 31-01-1991
0 </DIV>
<DIV>A 03-03-1991
1 </DIV>
<DIV>A 04-04-1991
0</DIV>
<DIV>A 06-08-1991
0</DIV>
<DIV>A 13-01-1992
1<BR>A 12-03-1992
1<BR>B
12-01-1988
0</DIV>
<DIV>B 19-03-1999
1</DIV>
<DIV>B 01-04-1999
0</DIV>
<DIV>B 09-06-2001
1</DIV>
<DIV><BR></DIV>
<DIV>The dates for a villages are in sorted order.Expected output:</DIV>
<DIV><BR></DIV>
<DIV>
<DIV><BR></DIV>
<DIV> City
Date Flag
</DIV></DIV></SPAN><SPAN class=Apple-style-span
style="FONT-SIZE: 13px; COLOR: rgb(51,51,51); FONT-FAMILY: arial, sans-serif; \
BORDER-COLLAPSE: collapse"> \
A \
03-03-1991 1 \
</SPAN><SPAN style="FONT-SIZE: 13px; COLOR: rgb(51,51,51); FONT-FAMILY: \
arial, sans-serif; BORDER-COLLAPSE: collapse"> <DIV>
<DIV> A
31-01-1991 -1
</DIV>
<DIV> A
04-04-1991 0</DIV>
<DIV> A
06-08-1991 -1 </DIV>
<DIV> A
13-01-1992 1<BR>
A
12-03-1992
1<BR>
B
12-01-1988
-1</DIV>
<DIV> B
19-03-1999 1</DIV>
<DIV> B
01-04-1999 -1</DIV>
<DIV> B
09-06-2001
1</DIV></DIV>
<DIV> </DIV>
<DIV>The (city,Date) is unique for each tuple.Hope I am able to state the
problem correctly.</DIV>
<DIV>Thanks Lalit</DIV>
<DIV></SPAN><FONT face=Arial size=2></FONT><BR> </DIV>
<DIV><FONT face=Arial size=2>* Hi, Lalit, </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>First, please always CC to mailing list. You may
get help faster from someone with more "savoir faire" than
me.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Second,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Try this out and tell me if it produced the
output you intended</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV><FONT face=Arial size=2>
<DIV><BR><FONT face="Courier New">UPDATE yourTable<BR>SET "Flag" =
-1<BR>FROM<BR>(<BR>SELECT x."Date" as "Date",x."City" as "City",x."Flag"
as "Flag" ,MAX(y."Date") as anterior<BR>FROM yourTable x<BR>JOIN
yourTable y<BR>ON y."City" = x."City"<BR>AND y."Date" < x."Date" <BR>GROUP
BY x."Date",x."City",x."Flag"<BR>) umq<BR>WHERE yourTable."City" = umq."City"
and anterior = yourTable."Date"<BR>AND yourTable."Flag" = 0<BR>AND umq."Flag"
= 1</FONT></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV>HTH,</DIV>
<DIV> </DIV>
<DIV>Best,</DIV>
<DIV>Oliver</FONT></DIV></BLOCKQUOTE></BODY></HTML>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic