From: | gorsa <gorsa(dot)1972(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | maximum count of contiguous years |
Date: | 2009-09-03 07:23:07 |
Message-ID: | 8da1618a0909030023h5a0eeca1ka22da96e1a38464b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
is there a way to get the maximum count of contiguous years? for example:
SELECT version();
PostgreSQL 8.3.1, compiled by Visual C++ 1400
CREATE TABLE sch_applform
(
scholar_id integer NOT NULL,
award_year numeric(4) NOT NULL,
CONSTRAINT sch_applform_pkey PRIMARY KEY (scholar_id, award_year)
)
WITH (OIDS=FALSE);
INSERT INTO sch_applform VALUES (1, 1994);
INSERT INTO sch_applform VALUES (1, 1995);
INSERT INTO sch_applform VALUES (1, 1996);
INSERT INTO sch_applform VALUES (1, 1997);
INSERT INTO sch_applform VALUES (1, 1999);
INSERT INTO sch_applform VALUES (1, 2000);
INSERT INTO sch_applform VALUES (1, 2001);
INSERT INTO sch_applform VALUES (2, 1994);
INSERT INTO sch_applform VALUES (2, 1996);
INSERT INTO sch_applform VALUES (2, 1997);
INSERT INTO sch_applform VALUES (2, 1998);
INSERT INTO sch_applform VALUES (2, 1999);
INSERT INTO sch_applform VALUES (2, 2000);
INSERT INTO sch_applform VALUES (2, 2002);
INSERT INTO sch_applform VALUES (3, 1994);
INSERT INTO sch_applform VALUES (3, 1995);
INSERT INTO sch_applform VALUES (3, 1997);
INSERT INTO sch_applform VALUES (3, 1998);
INSERT INTO sch_applform VALUES (3, 2000);
INSERT INTO sch_applform VALUES (3, 2001);
is there a select statement containing 'AND award_year BETWEEN 1994
AND 2002' that could generate the following?
scholar_id consistent_yrs
1 4
2 5
3 2
thanks in advance
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2009-09-03 07:44:04 | Does PG cache results of an aggregate function, (and results of non-volatile functions)? |
Previous Message | Brendan Jurd | 2009-09-03 06:41:07 | Re: Domain types versus anyelement |