>Hi guys.

Hi Bruce!

>I am having such data:
>
>ID DATE_TIME TYPE
>1 01.01.2015, 00:00:00.000 A
>2 01.01.2015, 00:01:00.000 A
>3 01.01.2015, 00:02:00.000 A
>4 01.01.2015, 00:10:00.000 B
>5 01.01.2015, 00:15:00.000 B
>6 01.01.2015, 00:20:00.000 A
>7 01.01.2015, 00:25:00.000 A
>
>Is it possible to write query which will result:
>
>TYPE DATE_MIN DATE_MAX
> A 01.01.2015, 00:00:00. 000 01.01.2015, 00:02:00.000
>
> B 01.01.2015, 00:10:00.000 01.01.2015, 00:15:00.000
>
> A 01.01.2015, 00:20:00.000 01.01.2015, 00:25:00.000
>
>As you can see I would like to have three groups and not two like it would have been with usage of standard GROUP BY. I know I can write a selectable stored procedure which >will result me such data but is it possible to do with new window functions and partitioning?
>
>Thanks.

Well, that's a rather unusual enquiry, but your particular question can be solved by rather standard SQL (probably even worked on InterBase before Firebird was born).

SELECT A."TYPE", A."DATE_TIME" as DATE_MIN, B."DATE_TIME" as DATE_MAX
FROM MyTable A
JOIN MyTable B
ON A."TYPE" = B."TYPE"
AND B.ID >= A.ID
LEFT JOIN MyTable ANot ON A.ID-1 = ANot.ID AND A."TYPE" = ANot."TYPE"
LEFT JOIN MyTable NotBetween ON NotBetween.ID BETWEEN A.ID AND B.ID AND A."TYPE" IS DISTINCT FROM NotBetween."TYPE"
LEFT JOIN MyTable BNot ON B.ID+1 = BNot.ID AND B."TYPE" = BNot."TYPE"
WHERE ANot.ID IS NULL
AND NotBetween.ID IS NULL
AND BNot.ID IS NULL

If there can be holes in the sequence - e.g. if the last record had ID 8 rather than 7 and you wanted it grouped with ID 6 even though there were no ID 7, things would still be feasible, but somewhat more complex.

Sorry for not using Windows functions - I'm still on Fb 2.5 and don't have them under my hood yet.

Set