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

Your script doesn't show how to get the "most recent" rows.
The output from the 1st SQL is :
id|customer|order_no
--+--------+--------
1| 1| 1
2| 1| 2
3| 1| 3
4| 2| 1
5| 2| 2
6| 3| 1

The output from the 2nd SQL is:
id|customer|order_no
--+--------+--------
2| 1| 2
5| 2| 2
6| 3| 1

What are you trying to show here?

I think you could just select the highest id, which means the latest data input.

Regards,
Chai

Brook Milligan wrote:

> 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;