pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brook Milligan <brook@trillium.NMSU.Edu>
To: jas1@scigen.co.uk
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Finding the "most recent" rows
Date: Thu, 22 Apr 1999 12:51:29 -0600 (MDT)
Message-ID: <199904221851.MAA10263@trillium.nmsu.edu> (raw)
In-Reply-To: <371F2ED6.29FD9251@scigen.co.uk>
References: <371F2ED6.29FD9251@scigen.co.uk>

   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: <199904221851.MAA10263@trillium.nmsu.edu>
Permalink:  ../199904221851.MAA10263@trillium.nmsu.edu/
Also on:    postgresql.org/message-id/199904221851.MAA10263@trillium.nmsu.edu

 · 

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: brook@trillium.NMSU.Edu, jas1@scigen.co.uk
  Subject: Re: [SQL] Finding the "most recent" rows
  In-Reply-To: <199904221851.MAA10263@trillium.nmsu.edu>
  References: <371F2ED6.29FD9251@scigen.co.uk> <199904221851.MAA10263@trillium.nmsu.edu>

* 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