Window functions are an extremely powerful powerful part of the SQL 2003 standard, supported by most modern releases of databases such as Oracle 8+, Postgres 9.1+, SQL Server 2005+ and others. Sadly neither SQLLite or MySql seem to support them yet, but if you are working with a database where they are available, do use them: they can make your life a lot easier. Generally, with window functions, you can write simpler and faster code than you would without.| Andrea Bergia's Website
In this post, we’re going to discuss a very useful SQL extension to work with hierarchal queries. This is sadly only implemented by Oracle and although a few other databases support it as well, it won’t work in PostgreSQL, MySql nor SQL Server. The dataset Let’s create a very simple hierarchal dataset to work with. CREATE TABLE GeoArea ( parentName VARCHAR2(100), code VARCHAR2(100) ); INSERT INTO GeoArea VALUES (NULL, 'World'); INSERT INTO GeoArea VALUES ('World', 'Europe'); INSERT INTO...| andreabergia.com