From: Chairudin Sentosa <chairudin@prima.net.id>
To: Brook Milligan <brook@trillium.NMSU.Edu>
Cc: jas1@scigen.co.uk
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Finding the "most recent" rows
Date: Fri, 23 Apr 1999 01:30:05 -0700
Message-ID: <37202F8D.B9479A67@prima.net.id> (raw)
References: <371F2ED6.29FD9251@scigen.co.uk>
<199904221851.MAA10263@trillium.nmsu.edu>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: chairudin@prima.net.id, brook@trillium.NMSU.Edu, jas1@scigen.co.uk
Subject: Re: [SQL] Finding the "most recent" rows
In-Reply-To: <37202F8D.B9479A67@prima.net.id>
References: <371F2ED6.29FD9251@scigen.co.uk> <199904221851.MAA10263@trillium.nmsu.edu> <37202F8D.B9479A67@prima.net.id>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox