FAQ
Hi,
I have a table with following sample data.

Village_name Date Label

A 1991-01-31 0

A 1991-04-28 0

A 1991-07-15 0

B 2000-02-15 0

B 2000-04-13 0

B 2001-09-13 0

C 1995-07-12 0

C 1995-10-13 0

I need to update the label to 1 when the number of days between two
dates(when compared with previous date) is greater than 120 for each
village.

Illustration:
For village A: row 1 date will not be compared to any date.
row 2 date will be compared to row 1 date and if
satisfies the condition label will be made 1.
row 3 compare to row 2 and continue this till last row
for village A.

Similarly first date for village B,C.

## Search Discussions

•  at Jun 17, 2011 at 11:10 am ⇧
Does your table has a primary key, or does it have just those three columns?
----- Original Message -----
From: LALIT KUMAR
To: [email protected]
Sent: Friday, June 17, 2011 11:17 AM
Subject: [NOVICE] compare date in two rows

Hi,
I have a table with following sample data.

Village_name Date Label

A 1991-01-31 0

A 1991-04-28 0

A 1991-07-15 0

B 2000-02-15 0

B 2000-04-13 0

B 2001-09-13 0

C 1995-07-12 0

C 1995-10-13 0

I need to update the label to 1 when the number of days between two dates(when compared with previous date) is greater than 120 for each village.

Illustration:
For village A: row 1 date will not be compared to any date.
row 2 date will be compared to row 1 date and if satisfies the condition label will be made 1.
row 3 compare to row 2 and continue this till last row for village A.

Similarly first date for village B,C.
•  at Jun 17, 2011 at 11:32 am ⇧
Hi, Kumar.

4get bout my previous question it is not really necessary.
Try this out and tell me if it solved your problem.

Best,
Oliveiros

UPDATE yourTable
SET "Label" = 1
FROM (
SELECT x."Date" as "Date",x."Village_name" as "Village_name",MAX(y."Date") as anterior
FROM yourTable x
JOIN yourTable y
ON y."Village_name" = x."Village_name"
AND y."Date" < x."Date"
GROUP BY x."Date",x."Village_name"
) umq
WHERE umq."Date" = yourTable."Date"
AND umq."Village_name" = yourTable."Village_name"
AND
yourTable."Date" - umq.anterior > 120
----- Original Message -----
From: LALIT KUMAR
To: [email protected]
Sent: Friday, June 17, 2011 11:17 AM
Subject: [NOVICE] compare date in two rows

Hi,
I have a table with following sample data.

Village_name Date Label

A 1991-01-31 0

A 1991-04-28 0

A 1991-07-15 0

B 2000-02-15 0

B 2000-04-13 0

B 2001-09-13 0

C 1995-07-12 0

C 1995-10-13 0

I need to update the label to 1 when the number of days between two dates(when compared with previous date) is greater than 120 for each village.

Illustration:
For village A: row 1 date will not be compared to any date.
row 2 date will be compared to row 1 date and if satisfies the condition label will be made 1.
row 3 compare to row 2 and continue this till last row for village A.

Similarly first date for village B,C.

## Related Discussions

Discussion Overview
 group pgsql-novice categories postgresql posted Jun 17, '11 at 10:17a active Jun 17, '11 at 11:32a posts 3 users 2 website postgresql.org irc #postgresql

### 2 users in discussion

Content

People

Support

Translate

site design / logo © 2023 Grokbase