I'd like an efficient way to pull out the most recent row (i.e. highest datatime) belonging to *each* of a number
ofplaces selected by a simple query.
The "Practical SQL Handbook" has a description of exactly what you are
looking for (don't have it handy or I'd give you the page number).
They discuss two ways to do it. One uses the HAVING clause with GROUP
BY (I think that is the section of the book to look in), but I don't
think psql supports this. The other way uses a subselect which is
supported by psql.
The script at the bottom illustrates some of the ideas.
Cheers,
Brook
===========================================================================
/* -*- C -*-* recent.sql*/
/** find the most recent entry (order) for each group (customer)*/
-- create tables
drop sequence invoices_id_seq;
drop table invoices;
create table invoices
(id serial,customer int,order_no int,
unique (customer, order_no)
);
insert into invoices (customer, order_no) values (1, 1);
insert into invoices (customer, order_no) values (1, 2);
insert into invoices (customer, order_no) values (1, 3);
insert into invoices (customer, order_no) values (2, 1);
insert into invoices (customer, order_no) values (2, 2);
insert into invoices (customer, order_no) values (3, 1);
select * from invoices order by customer, order_no;
select * from invoices rwhere order_no = (select max (order_no) from invoices r2 where r.customer = r2.customer and
order_no< 3)order by r.customer, r.order_no;