Re: [GENERAL] maximum count of contiguous years

[gorsa]

thanks tim. will read up on rank() and pl/pgsql.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] maximum count of contiguous years

[Tim Landscheidt]

gorsa  wrote:

> [...]
> is there a select statement containing 'AND award_year BETWEEN 1994
> AND 2002' that could generate the following?
> scholar_idconsistent_yrs
>  1 4
>  2 5
>  3 2

You could either do some wild fancy query where you parti-
tion the data by scholar_id, then by award_year, then filter
on the condition that the sum of award_year and RANK() (?)
less one equals the current award_year, find the maximum of
those, ...

  ... or you could just write a short function in your ap-
plication (or a set-returning PL/pgSQL function if your ap-
plication is dumb).

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] maximum count of contiguous years

[gorsa]

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_idconsistent_yrs
 1 4
 2 5
 3 2

thanks in advance

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general