>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
