Not fully sure on the syntax of the Window to accomplish the specified goal
- and am not sure it would be any cleaner anyway.
But, the reason I am responding is how you phrased "...windows only compared
groups of records in the same table".
When I say:
FROM tableA
* JOIN tableB
I have now effectively created a new "table" in the sense that anything I
can do on tableA or tableB I can also do on the result of the joining of
those two tables.
Re-reading the comment and context it probably would be more fair to guess
that you know this. In the context of a Window function you can use it
AFTER you perform a LEFT JOIN - but you may not need to do the "SELECT id,
count(*) GROUP BY id" on the child table but instead can just do a direct
left join onto child and use a Window.
Again, mostly just speculation and for the moment I don't have the time to
spare to try out a Window based solution; especially since my gut says the
LEFT JOIN on the grouped child is likely the best solution anyway.
David J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Orr
Sent: Thursday, March 31, 2011 3:20 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Counting records in a child table
Thanks. How would I do it with a window function? I thought windows only
compared groups of records in the same table.