Packing rows

So worth tasting. Having a Keisari Strong Munchener 5,7% brewed by Nokian panimo. Figuring out a thing done today.

The case was that we have a relation that has the only one over time feature present. There is the possibility that the same attribute is the same in several continuous rows. And we want to put those rows together in a query result. So the question here was how to implement pack operator presented in C.J. Date book Temporal data and the relational model. Some good alternatives are presented in Joe Celko's thinking in sets: auxiliary, temporal, and virtual tables in SQL. He presents a OLAP Function Solution. Here we have the not overlapping present so the question is a bit simpler. And to a example...

CREATE TABLE Z(Z NUMBER(16) NOT NULL
             , VALIDFROM NUMBER(4) NOT NULL
             , VALIDTILL NUMBER(4) NOT NULL
);

INSERT INTO Z VALUES (1,2000,2003);

INSERT INTO Z VALUES (1,2003,2004);

INSERT INTO Z VALUES (2,2004,2005);

INSERT INTO Z VALUES (2,2005,2008);

INSERT INTO Z VALUES (1,2008,2010);

SELECT Z, MINVALIDFROM, MAX(VALIDTILL) MAXVALIDTILL
  FROM (
  SELECT Z, VALIDFROM,VALIDTILL,PREVTILL
       , MAX(CASE WHEN VALIDFROM <= PREVTILL
                  THEN NULL
                  ELSE VALIDFROM
              END) 
         OVER ( PARTITION BY Z ORDER BY VALIDFROM, VALIDTILL
                ROWS UNBOUNDED PRECEDING) MINVALIDFROM
    FROM (
    SELECT Z
         , VALIDFROM,VALIDTILL
         , LAG(VALIDTILL) 
           OVER (PARTITION BY Z ORDER BY VALIDTILL) PREVTILL
      FROM Z
   ) 
)
GROUP BY Z, MINVALIDFROM
ORDER BY MINVALIDFROM, Z
;

1 2000 2004
2 2004 2008
1 2008 2010

The simpler part is to use LAG.