Re: [SQL] Finding the "most recent" rows

I'd like an efficient way to pull out the most recent row (i.e. highest
datatime) belonging to *each* of a number of places 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 r
where 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;