Re: Joining time fields?

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: oliveiros(dot)cristina(at)asperger-talents(dot)com
Subject: Re: Joining time fields?
Date: 2012-07-26 20:06:50
Message-ID: 5011A35A.7070605@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Jonatan Reiners 2012-07-27 09:35:35 problem with pg_dump
Previous Message Bryan Lee Nuse 2012-07-26 19:35:17 Re: Joining time fields?