People have asked us how to calculate running totals a number of times; not a lot but enough that we feel we should document the general technique.
This approach is fairly ANSI-SQL standard and involves using SELF JOINS. In a later article we shall describe how to calculate moving averages which
follows a similar technique but with some extra twists.
Note that the below examples can also be done with a correlated sub-select in the SELECT clause and in some cases that sometimes works better. Perhaps
we shall show that approach in a later issue.
We tend to prefer the look of the SELF JOIN though and in practice it is generally more efficient since its easier for planners to optimize and doesn't always result in a nested loop strategy.
Just feels a little cleaner and if you are totaling a lot of columns (e.g number of items, products) etc,
much more efficient.
We would like to thank Jeff Crumbley of IILogistics for providing many of these steps
and informing us that Microsoft has finally released a
64-bit OLEDB for ODBC driver.
For those who have not experienced the torture of this situation - let me start with a little background.
First if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. If
however you had a SQL Server 2005 64-bit server, you ran into 2 very annoying obstacles.
Obstacle 1: There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated
when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.
Obstacle 2: All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider
for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft.
Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.
People have asked us on several occasions if there is such a construct
SELECT * EXCEPT(...list) FROM sometable. Sadly we do not think such a
thing exists in the ANSI SQL Specs nor in PostgreSQL.
The above feature would come in handy when you have certain fields in your tables that are common
across tables, but you need to leave them out in your query. A common case of this is when you have PostGIS tables loaded using shp2pgsql
with a fields called gid and the_geom which are not terribly useful for simple data queries.
There are 2 common ways we use to achieve this result.
Using PgAdmin's CREATE SELECT script feature. This exists in other GUI tools as well.
Using an Information Schema script hack to construct the SELECT statement