pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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;




view thread (24+ messages)  latest in thread

Message-ID: <37202F8D.B9479A67@prima.net.id>
Permalink:  ../37202F8D.B9479A67@prima.net.id/
Also on:    postgresql.org/message-id/37202F8D.B9479A67@prima.net.id

 · 

reply

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