On 07/26/2012 12:14 PM, Oliveiros d'Azevedo Cristina wrote:
Howdy, Bryan,
I am not familiar with many constructs you employed, like crossed, grouped and using.
What is the version of PostGreSQL you are using? I'm still using 8.3
If your query works and your print out does prove so, then you found James an elegant solution.
I'm an old dog but I must find the time to upgrade myself, I'm missing lots of constructs. I'm always trying to do queries with the concepts I know and I'm
aware that probably new versions bring new constructs that can make in one line what I do in 10 and in a much more efficient way.
The construct you want to research is the common-table-expression (CTE).
A common table expression acts sort of like a temporary table or view that is created just for the duration of that query. The "with crossed as (...)" and "with grouped as (...)" make temporary "tables" (table expressions) called "crossed" and "grouped" that are referenced in the final part of the query as though they were tables. "Crossed" and "grouped" are just names assigned to the CTEs, not new constructs.
An exciting capability provided through the use of CTEs is the ability to write recursive queries which greatly simplifies dealing with hierarchical data like org-charts.
Common table expressions (
http://www.postgresql.org/docs/current/static/queries-with.html) along with windowing functions (
http://www.postgresql.org/docs/current/static/tutorial-window.html) are two very useful features that were introduced in version 8.4 (
http://www.postgresql.org/docs/8.4/static/release-8-4.html).
Go forth and upgrade! (8.3 is EOL in a few months, anyway.)
Cheers,
Steve