From: | "listrec" <listrec(at)epecon(dot)de> |
---|---|
To: | "Erik G(dot) Burrows" <eburrows(at)erikburrows(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SQL problem: bank account |
Date: | 2003-06-02 07:49:55 |
Message-ID: | PCEJJIGCHIJCOOOGFAFEKENBCAAA.listrec@epecon.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
How about:
select max(transaction_id) from bank_account group by customer_id
Should work...
Detlef
-----Ursprüngliche Nachricht-----
Von: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]Im Auftrag von Erik G. Burrows
Gesendet: Montag, 2. Juni 2003 07:30
An: pgsql-sql(at)postgresql(dot)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(at)erikburrows(dot)com(dot)pgpkey
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2003-06-02 07:51:52 | Re: CASE returning multiple values (was SQL Help) |
Previous Message | Sean Chittenden | 2003-06-02 06:21:37 | Re: SQL problem: bank account |