FAQ
Hi,
I have following table data:
site_id village_name Date depth flag

W001 Akoli 1991-01-31 3.5
W001 Akoli 1991-03-31 3.7
W001 Akoli 1991-05-31 4.35
W001 Akoli 1992-01-31 2.9
W001 Akoli 1992-03-31 4
W001 Akoli 1992-05-31 2.0
W001 Akoli 1992-10-31 2
W0002 Awale 1977-05-30 7.2
W0002 Awale 1977-10-30 3

For each village the following rule is to be followed.

The depth in the month of may (05) must be more than depth given in the
previous reading. If not so the flag field shoul be set 1

The tuple in green has month may(05) which has depth more than previous date
depth.

The red tuple has month may(05) but has depth less than previous depth.

So the flag field is to be set 1 here.

Simiarly for other villages (i.e.) dates from two different villages will
not be compared.

## Search Discussions

•  at Jun 18, 2011 at 7:10 pm ⇧

LALIT KUMAR wrote on 18.06.2011 20:31:
Hi,
I have following table data:
site_id village_name Date depth flag

W001 Akoli 1991-01-31 3.5
W001 Akoli 1991-03-31 3.7
W001 Akoli 1991-05-31 4.35
W001 Akoli 1992-01-31 2.9
W001 Akoli 1992-03-31 4
W001 Akoli 1992-05-31 2.0
W001 Akoli 1992-10-31 2
W0002 Awale 1977-05-30 7.2
W0002 Awale 1977-10-30 3

For each village the following rule is to be followed.

The depth in the month of may (05) must be more than depth given in the previous reading. If not so the flag field shoul be set 1

The tuple in green has month may(05) which has depth more than previous date depth.

The red tuple has month may(05) but has depth less than previous depth.

So the flag field is to be set 1 here.

Simiarly for other villages (i.e.) dates from two different villages will not be compared.
Something like:

SELECT site_id,
village_name,
date,
depth,
case
when depth < lag(depth) over (partition by village order by date asc) then 1
else 0
as flag
FROM the_table

## Related Discussions

Discussion Overview
 group pgsql-novice categories postgresql posted Jun 18, '11 at 6:32p active Jun 18, '11 at 7:10p posts 2 users 2 website postgresql.org irc #postgresql

### 2 users in discussion

Content

People

Support

Translate

site design / logo © 2023 Grokbase