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|       12|       1|       23|       1|       34|       2|       15|       2|       26|
   3|       1
 

The output from the 2nd SQL is:
id|customer|order_no
--+--------+--------2|       1|       25|       2|       26|       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;