X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id 76623926294
	for <pgsql-sql@postgresql.org>; Mon,  2 Jun 2003 03:50:28 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 14405-01 for <pgsql-sql@postgresql.org>;
	Mon,  2 Jun 2003 03:50:17 -0400 (EDT)
Received: from www.epecon.de (www.epecon.de [194.174.52.125])
	by developer.postgresql.org (Postfix) with ESMTP id 4440E92628A
	for <pgsql-sql@postgresql.org>; Mon,  2 Jun 2003 03:50:14 -0400 (EDT)
From: "listrec" <listrec@epecon.de>
To: "Erik G. Burrows" <eburrows@erikburrows.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: SQL problem: bank account
Date: Mon, 2 Jun 2003 09:49:55 +0200
Message-ID: <PCEJJIGCHIJCOOOGFAFEKENBCAAA.listrec@epecon.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0)
In-Reply-To: <1054531815.13212.27.camel@griffin.jedi-group.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Importance: Normal
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200306/14
X-Sequence-Number: 13772

How about:

select max(transaction_id) from bank_account group by customer_id

Should work...

Detlef

-----Ursprüngliche Nachricht-----
Von: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]Im Auftrag von Erik G. Burrows
Gesendet: Montag, 2. Juni 2003 07:30
An: pgsql-sql@postgresql.org
Betreff: [SQL] SQL problem: bank account


It seems to me this is a simple problem, but the solution eludes me.

I have a table:

bank_account (
  transaction_id int not null serial,
  customer_id int not null references customer(id),
  ts timestamp not null default now(),
  amount float not null,
  balance float not null,
  primary key(transaction_id)
)

I need to get the most recent transaction for each customer. I need only
the transaction ID, but the entire row would be best.



I have two solutions, both of which are too slow for use in my
interactive web-based interface:

Solution1: Outer left self join:
SELECT
  ba1.*
FROM
  bank_account ba1
  LEFT OUTER JOIN bank_account ba2
    ON ba1.customer_id = ba2.customer_id AND ba1.ts < ba2.ts
WHERE
  ba2.ts IS NULL;

This query works great on tables of less than a few thousand rows. For
my 300k row table, it takes several hours.


Solution2: max-concat trick
SELECT
  split_part(max(
      extract(EPOCH from ts)::VARCHAR || '    ' ||
      transaction_id::VARCHAR), '    ', 2)::INT
FROM
  bank_account
GROUP BY
  customer_id

This is an ugly and obviously inefficient solution, but it does the job
in about 1/2 hour. Still too long though.



I've been working on this problem for days, and consulting friends. No
elegant, fast solution is presenting itself. As I said, I feel I'm not
seeing the obvious solution in front of my face.

In the mean-time I can use this query to do the job on a per-customer
basis:

select
  *
from
  bank_account
where
  id = <the customer's ID>
  and ts = (select max(ts) from bank_account ba2 where ba2.customer_id =
bank_account.customer_id);


However, doing this for all 40,000 customers is not workable as a manual
process. My last resort is to do it this way to pre-generate the report,
but I'd far rather do it real-time.

Help! My brain hurts!

--
Erik G. Burrows - KG6HEA                   www.erikburrows.com
PGP Key: http://www.erikburrows.com/egb@erikburrows.com.pgpkey

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly