maximum count of contiguous years

Поиск
Список
Период
Сортировка
От gorsa
Тема maximum count of contiguous years
Дата
Msg-id 8da1618a0909030023h5a0eeca1ka22da96e1a38464b@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Brendan Jurd
Дата:
Сообщение: Re: Domain types versus anyelement
Следующее
От: Allan Kamau
Дата:
Сообщение: Does PG cache results of an aggregate function, (and results of non-volatile functions)?