Re: Subqueries - performance and use question

try this approach:

SELECT
c.id,
c.firstname,
c.lastname,
a.latest_billdate
FROM
customers c
INNER JOIN -- or LEFT if you want the NULLs
(
SELECT
customer_id,
max(billdate) as latest_billdate
FROM
ar
) a
ON
c.customerid = a.customerid
WHERE
c.status = 'new';

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Demel, Jeff
> Sent: Thursday, February 01, 2007 8:08 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Subqueries - performance and use question
>
> I need some basic advice on how to run a subquery, or if there's a
> better way. Let me set up a situation, and get some advice
> on it. This
> is my first post on this list, so I hope this kind of noob question is
> ok.
>
> Say I have a table of customers and table of accounts receivable
> transactions, There is a one-to-many relationship between the two
> (obviously a customer can have more than one purchase/transaction).
>
> I want to run a query where I pull a set of customers based on some
> parameter like, for sake of an example, where their status = new, and
> also pull the most recent billing date from the accounts receivable
> table.
>
> Here's what I came up with:
>
> SELECT customers.id, customers.firstname,
> customers.lastname, customers.phone number,
> (SELECT ar.billdate FROM ar
> WHERE customers.customerid = ar.customerid
> ORDER BY ar.billdate LIMIT 1)
> AS lastarbilldate
> FROM customers
> WHERE customers.status = 'new';
>
> As you can see, I'm using a subquery here to get the latest billing
> date. My question is twofold. Is this subquery style the only way to
> get one record in a one-to-many relationship, or is there a
> way to do it
> with a join? Also, if this is fine, is there a way to do it
> that would
> get the same results but be faster? It may not matter on a small
> database, but if you've got millions of customers and transactions, a
> subquery can get expensive.
>
> I'm just looking for some basic direction. I hope my fake
> example makes
> sense.
>
> I'm running PostgreSQL 8.1, on a Windows 2003 server.
>
> TIA
>
> -Jeff
> This email is intended only for the individual or entity to
> which it is addressed. This email may contain information
> that is privileged, confidential or otherwise protected from
> disclosure. Dissemination, distribution or copying of this
> e-mail or any attachments by anyone other than the intended
> recipient, or an employee or agent responsible for delivering
> the message to the intended recipient, is prohibited. If you
> are not the intended recipient of this message or the
> employee or agent responsible for delivery of this email to
> the intended recipient, please notify the sender by replying
> to this message and then delete it from your system. Any
> use, dissemination, distribution, or reproduction of this
> message by unintended recipients is strictly prohibited and
> may be unlawful.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>