> 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.
For the sake of being explicit, change your table definition (though what you have above is a-okay and works): CREATE SEQUENCE transaction_id_seq; CREATE TABLE bank_account ( transaction_id int not null DEFAULT NEXTVAL('transaction_id_seq'::TEXT), 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) ); Once you insert a value into the bank_account table, SELECT CURRVAL('transaction_id_seq') will be what you're looking for. Read up on CURRVAL() at: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-sequence.html -sc -- Sean Chittenden ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html