Re: compare date in two rows

----- Original Message -----

From: LALIT KUMAR

To: pgsql-novice@postgresql.org

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.