pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding the "most recent" rows
24+ messages / 9 participants
[nested] [flat]
* Finding the "most recent" rows
@ 1999-04-22 14:14 Julian Scarfe <jas1@scigen.co.uk>
0 siblings, 3 replies; 24+ messages in thread
From: Julian Scarfe @ 1999-04-22 14:14 UTC (permalink / raw)
To: pgsql-sql
I have a table (representing a set of observations) with datetime fields and a
non-unique place field.
e.g.
create table obs (
the_time datetime,
the_place char(8),
...other fields...
)
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.
e.g. given a table such as:
the_time the_place ...
0910 London
1130 London
0910 Paris
0930 London
0840 Paris
1020 London
0740 Paris
I'd like to select:
1130 London
0910 Paris
Most of my attempts at this (as an SQL novice) feel very clumsy and
inefficient. Is there an efficient way of doing this in SQL?
--
Julian Scarfe
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-22 14:45 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Julian Scarfe <jas1@scigen.co.uk>
2 siblings, 1 reply; 24+ messages in thread
From: Chris Bitmead @ 1999-04-22 14:45 UTC (permalink / raw)
To: pgsql-sql
Try
SELECT the_place, max(the_time) FROM the_place GROUP BY the_place;
Julian Scarfe wrote:
>
> I have a table (representing a set of observations) with datetime fields and a
> non-unique place field.
>
> e.g.
> create table obs (
> the_time datetime,
> the_place char(8),
> ...other fields...
> )
>
> 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.
>
> e.g. given a table such as:
>
> the_time the_place ...
> 0910 London
> 1130 London
> 0910 Paris
> 0930 London
> 0840 Paris
> 1020 London
> 0740 Paris
>
> I'd like to select:
> 1130 London
> 0910 Paris
>
> Most of my attempts at this (as an SQL novice) feel very clumsy and
> inefficient. Is there an efficient way of doing this in SQL?
> --
>
> Julian Scarfe
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-22 14:46 George Moga <george@flex.ro>
parent: Julian Scarfe <jas1@scigen.co.uk>
2 siblings, 0 replies; 24+ messages in thread
From: George Moga @ 1999-04-22 14:46 UTC (permalink / raw)
To: Julian Scarfe <jas1@scigen.co.uk>; pgsql-sql
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
Julian Scarfe wrote:
<blockquote TYPE=CITE>I have a table (representing a set of observations)
with datetime fields and a
<br>non-unique place field.
<p>e.g.
<br>create table obs (
<br>the_time datetime,
<br>the_place char(8),
<br>...other fields...
<br>)
<p>I'd like an efficient way to pull out the most recent row (i.e. highest
<br>datatime) belonging to *each* of a number of places selected by a simple
<br>query.
<p>e.g. given a table such as:
<p>the_time the_place ...
<br>0910 London
<br>1130 London
<br>0910 Paris
<br>0930 London
<br>0840 Paris
<br>1020 London
<br>0740 Paris
<p>I'd like to select:
<br>1130 London
<br>0910 Paris
<p>Most of my attempts at this (as an SQL novice) feel very clumsy and
<br>inefficient. Is there an efficient way of doing this in SQL?
<br>--
<p>Julian Scarfe</blockquote>
If I understund the problem try this:
<br> <tt></tt>
<p><tt>test=> create table test (time datetime default now(), place char(16));</tt>
<br><tt>CREATE</tt>
<br><tt>test=> insert into test (place) values ('London');</tt>
<br><tt>INSERT 194824 1</tt>
<br><tt>test=> insert into test (place) values ('Paris');</tt>
<br><tt>INSERT 194825 1</tt>
<br><tt>.............</tt>
<br><tt>test=> insert into test (place) values ('Berlin');</tt>
<br><tt>INSERT 194835 1</tt>
<br><tt>test=> insert into test (place) values ('London');</tt>
<br><tt>INSERT 194836 1</tt>
<br><tt>test=> insert into test (place) values ('Berlin');</tt>
<br><tt>INSERT 194837 1</tt>
<br><tt>test=> select * from test;</tt>
<br><tt>time
|place</tt>
<br><tt>-----------------------------+----------------</tt>
<br><tt>Thu 22 Apr 17:33:23 1999 EEST|London</tt>
<br><tt>Thu 22 Apr 17:33:30 1999 EEST|Paris</tt>
<br><tt>Thu 22 Apr 17:33:36 1999 EEST|London</tt>
<br><tt>Thu 22 Apr 17:33:49 1999 EEST|Madrid</tt>
<br><tt>Thu 22 Apr 17:33:54 1999 EEST|Paris</tt>
<br><tt>Thu 22 Apr 17:34:03 1999 EEST|Berlin</tt>
<br><tt>Thu 22 Apr 17:34:05 1999 EEST|Madrid</tt>
<br><tt>Thu 22 Apr 17:34:08 1999 EEST|London</tt>
<br><tt>Thu 22 Apr 17:34:12 1999 EEST|Paris</tt>
<br><tt>Thu 22 Apr 17:34:14 1999 EEST|Madrid</tt>
<br><tt>Thu 22 Apr 17:34:16 1999 EEST|Paris</tt>
<br><tt>Thu 22 Apr 17:34:20 1999 EEST|Berlin</tt>
<br><tt>Thu 22 Apr 17:34:22 1999 EEST|London</tt>
<br><tt>Thu 22 Apr 17:34:31 1999 EEST|Berlin</tt>
<br><tt>(14 rows)</tt><tt></tt>
<p><tt>test=> select place, time from test t where time = (select max(s.time)
from test s where s.place = t.place) order by place;</tt>
<br><tt>place
|time</tt>
<br><tt>----------------+-----------------------------</tt>
<br><tt>Berlin |Thu
22 Apr 17:34:31 1999 EEST</tt>
<br><tt>London |Thu
22 Apr 17:34:22 1999 EEST</tt>
<br><tt>Madrid |Thu
22 Apr 17:34:14 1999 EEST</tt>
<br><tt>Paris
|Thu 22 Apr 17:34:16 1999 EEST</tt>
<br><tt>(4 rows)</tt><tt></tt>
<p><tt>test=></tt>
<p>I use PostgreSQL 6.5.0 beta1 on Red Hat LINUX 5.2 with 2.2.2 kernel.
<p>-- <br>
Best,<br>
George Moga,<br>
george@flex.ro<br>
Braila, ROMANIA
<br> </html>
^ permalink raw reply [nested|flat] 24+ messages in thread
* RE: [SQL] Finding the "most recent" rows
@ 1999-04-22 14:53 Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 1 reply; 24+ messages in thread
From: Michael J Davis @ 1999-04-22 14:53 UTC (permalink / raw)
To: 'Chris Bitmead' <chris.bitmead@bigfoot.com>; pgsql-sql
Try
select * from the_place order by the_time desc limit 2;
-----Original Message-----
From: Chris Bitmead [SMTP:chris.bitmead@bigfoot.com]
Sent: Thursday, April 22, 1999 8:45 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Finding the "most recent" rows
Try
SELECT the_place, max(the_time) FROM the_place GROUP BY the_place;
Julian Scarfe wrote:
>
> I have a table (representing a set of observations) with datetime
fields and a
> non-unique place field.
>
> e.g.
> create table obs (
> the_time datetime,
> the_place char(8),
> ...other fields...
> )
>
> 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.
>
> e.g. given a table such as:
>
> the_time the_place ...
> 0910 London
> 1130 London
> 0910 Paris
> 0930 London
> 0840 Paris
> 1020 London
> 0740 Paris
>
> I'd like to select:
> 1130 London
> 0910 Paris
>
> Most of my attempts at this (as an SQL novice) feel very clumsy
and
> inefficient. Is there an efficient way of doing this in SQL?
> --
>
> Julian Scarfe
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-22 15:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 2 replies; 24+ messages in thread
From: Tom Lane @ 1999-04-22 15:29 UTC (permalink / raw)
To: pgsql-sql
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> Julian Scarfe 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.
>
> Try
> SELECT the_place, max(the_time) FROM the_place GROUP BY the_place;
But I'll bet he wants the whole row containing the max time, not just
the place and time columns. I've run into similar problems and never
felt like I had a clean solution, either.
You could do something like
SELECT * FROM table AS t1 WHERE NOT
EXISTS(SELECT * FROM table AS t2 WHERE
t2.place = t1.place AND t2.time > t1.time);
but this is ugly, and probably horribly inefficient as well. (It might
not be unacceptably slow if the table has indexes on place and time,
but it sure looks like a brute-force approach.)
What you'd really like is something like a SELECT DISTINCT with a user-
specifiable row comparison operator; then you'd just "ORDER BY place, time"
and make a comparator that discards all but the last row for each place
value. Hmm ... a little experimentation suggests that
SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC;
might do the right thing. It *seems* to select the first row for each
value of place. I've never seen a spec for this feature, however, so
I'm not sure if it's reliable or not...
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-22 18:51 Brook Milligan <brook@trillium.NMSU.Edu>
parent: Julian Scarfe <jas1@scigen.co.uk>
2 siblings, 1 reply; 24+ messages in thread
From: Brook Milligan @ 1999-04-22 18:51 UTC (permalink / raw)
To: jas1@scigen.co.uk; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-22 23:57 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 24+ messages in thread
From: Chris Bitmead @ 1999-04-22 23:57 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC;
Clever. But why doesn't this work....
select title, summary, time from story t where time = (select
max(s.time) from story s GROUP BY s.title);
ERROR: parser: Subselect has too many or too few fields.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 00:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 1999-04-23 00:45 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> Clever. But why doesn't this work....
> select title, summary, time from story t where time = (select
> max(s.time) from story s GROUP BY s.title);
> ERROR: parser: Subselect has too many or too few fields.
A subselect used in an expression has to return exactly one value;
yours will return as many tuples as there are distinct titles.
I think you meant
select title, summary, time from story t where time = (select
max(s.time) from story s WHERE s.title = t.title);
Here the subselect should give a single result each time it's
executed. Unfortunately, it's gonna be executed once for each
tuple scanned by the outer select :-(
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 02:00 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Chairudin Sentosa <chairudin@prima.net.id>
0 siblings, 0 replies; 24+ messages in thread
From: Chris Bitmead @ 1999-04-23 02:00 UTC (permalink / raw)
To: chairudin@prima.net.id; +Cc: Michael J Davis <michael.j.davis@tvguide.com>; pgsql-sql
I don't think you can be using 6.5 even if you think you are. Make sure
you have 6.5 and everything is pointing to 6.5 including postmaster.
Chairudin Sentosa wrote:
>
> I tried this, and did not work.
>
> ibs=> select * from ibs_billing_record order by start_time desc limit 2;
> ERROR: parser: syntax error at or near "limit"
>
> Regards,
> Chai
>
> Michael J Davis wrote:
>
> > Try
> > select * from the_place order by the_time desc limit 2;
> >
> > -----Original Message-----
> > From: Chris Bitmead [SMTP:chris.bitmead@bigfoot.com]
> > Sent: Thursday, April 22, 1999 8:45 AM
> > To: pgsql-sql@postgresql.org
> > Subject: Re: [SQL] Finding the "most recent" rows
> >
> > Try
> > SELECT the_place, max(the_time) FROM the_place GROUP BY the_place;
> >
> > Julian Scarfe wrote:
> > >
> > > I have a table (representing a set of observations) with datetime
> > fields and a
> > > non-unique place field.
> > >
> > > e.g.
> > > create table obs (
> > > the_time datetime,
> > > the_place char(8),
> > > ...other fields...
> > > )
> > >
> > > 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.
> > >
> > > e.g. given a table such as:
> > >
> > > the_time the_place ...
> > > 0910 London
> > > 1130 London
> > > 0910 Paris
> > > 0930 London
> > > 0840 Paris
> > > 1020 London
> > > 0740 Paris
> > >
> > > I'd like to select:
> > > 1130 London
> > > 0910 Paris
> > >
> > > Most of my attempts at this (as an SQL novice) feel very clumsy
> > and
> > > inefficient. Is there an efficient way of doing this in SQL?
> > > --
> > >
> > > Julian Scarfe
> >
> > --
> > Chris Bitmead
> > http://www.bigfoot.com/~chris.bitmead
> > mailto:chris.bitmead@bigfoot.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 02:30 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Chris Bitmead @ 1999-04-23 02:30 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
>
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > Clever. But why doesn't this work....
>
> > select title, summary, time from story t where time = (select
> > max(s.time) from story s GROUP BY s.title);
> > ERROR: parser: Subselect has too many or too few fields.
>
> A subselect used in an expression has to return exactly one value;
> yours will return as many tuples as there are distinct titles.
Why doesn't replacing "=" with "IN" produce a result? It wouldn't be the
desired result, but I thought this was legal.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 04:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 2 replies; 24+ messages in thread
From: Tom Lane @ 1999-04-23 04:08 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
>>>> select title, summary, time from story t where time = (select
>>>> max(s.time) from story s GROUP BY s.title);
> Why doesn't replacing "=" with "IN" produce a result? It wouldn't be the
> desired result, but I thought this was legal.
I thought so too (on both counts). Are you saying it doesn't work?
What happens? Which version are you using?
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 04:10 Brook Milligan <brook@trillium.NMSU.Edu>
parent: Chairudin Sentosa <chairudin@prima.net.id>
0 siblings, 0 replies; 24+ messages in thread
From: Brook Milligan @ 1999-04-23 04:10 UTC (permalink / raw)
To: chairudin@prima.net.id; +Cc: jas1@scigen.co.uk; pgsql-sql
Your script doesn't show how to get the "most recent" rows.
True enough, but that's just because of the < 3 condition within the
subselect. Take that out and you'll get all the most recent rows (in
this example that means largest order_no, but the same works with
dates or whatever).
What are you trying to show here?
The first query was just showing the table, the second the action of
the relevant select.
I thought this was what you wanted and that you would recognize the
effect of the < 3 condition. I just happened to have this example
already that illustrated the general idea and expected that you would
get the idea. Sorry if I should have been more complete.
I think you could just select the highest id, which means the latest data input.
In this particular case the id and the order_no column happen to yield
the same results (they are both ordered in the same way). That
generally won't be the case. But, if you want the highest id within
each customer category a simple change to the select below will do it.
In any case, to get the largest (or most recent or whatever) anything
in each category you need to do a condition involving a subselect with
an aggregate. Something like the following will give you the largest
(without additional constraint) order_no within each customer.
select * from invoices r
where order_no = (select max (order_no) from invoices r2 where r.customer = r2.customer)
order by r.customer, r.order_no;
Sorry for the confusion.
Cheers,
Brook
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 04:39 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 24+ messages in thread
From: Chris Bitmead @ 1999-04-23 04:39 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
>
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> >>>> select title, summary, time from story t where time = (select
> >>>> max(s.time) from story s GROUP BY s.title);
>
> > Why doesn't replacing "=" with "IN" produce a result? It wouldn't be the
> > desired result, but I thought this was legal.
>
> I thought so too (on both counts). Are you saying it doesn't work?
> What happens? Which version are you using?
httpd=> select title, summary, time from story t where time IN (select
max(s.time) from story s GROUP BY s.title);
ERROR: parser: Subselect has too many or too few fields.
I'm using postgresql-snap-990329.tgz
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 08:30 Chairudin Sentosa <chairudin@prima.net.id>
parent: Brook Milligan <brook@trillium.NMSU.Edu>
0 siblings, 1 reply; 24+ messages in thread
From: Chairudin Sentosa @ 1999-04-23 08:30 UTC (permalink / raw)
To: Brook Milligan <brook@trillium.NMSU.Edu>; +Cc: jas1@scigen.co.uk; pgsql-sql
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;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 08:34 Chairudin Sentosa <chairudin@prima.net.id>
parent: Michael J Davis <michael.j.davis@tvguide.com>
0 siblings, 1 reply; 24+ messages in thread
From: Chairudin Sentosa @ 1999-04-23 08:34 UTC (permalink / raw)
To: Michael J Davis <michael.j.davis@tvguide.com>; +Cc: 'Chris Bitmead' <chris.bitmead@bigfoot.com>; pgsql-sql
I tried this, and did not work.
ibs=> select * from ibs_billing_record order by start_time desc limit 2;
ERROR: parser: syntax error at or near "limit"
Regards,
Chai
Michael J Davis wrote:
> Try
> select * from the_place order by the_time desc limit 2;
>
> -----Original Message-----
> From: Chris Bitmead [SMTP:chris.bitmead@bigfoot.com]
> Sent: Thursday, April 22, 1999 8:45 AM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Finding the "most recent" rows
>
> Try
> SELECT the_place, max(the_time) FROM the_place GROUP BY the_place;
>
> Julian Scarfe wrote:
> >
> > I have a table (representing a set of observations) with datetime
> fields and a
> > non-unique place field.
> >
> > e.g.
> > create table obs (
> > the_time datetime,
> > the_place char(8),
> > ...other fields...
> > )
> >
> > 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.
> >
> > e.g. given a table such as:
> >
> > the_time the_place ...
> > 0910 London
> > 1130 London
> > 0910 Paris
> > 0930 London
> > 0840 Paris
> > 1020 London
> > 0740 Paris
> >
> > I'd like to select:
> > 1130 London
> > 0910 Paris
> >
> > Most of my attempts at this (as an SQL novice) feel very clumsy
> and
> > inefficient. Is there an efficient way of doing this in SQL?
> > --
> >
> > Julian Scarfe
>
> --
> Chris Bitmead
> http://www.bigfoot.com/~chris.bitmead
> mailto:chris.bitmead@bigfoot.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 10:30 Julian Scarfe <jas1@scigen.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 24+ messages in thread
From: Julian Scarfe @ 1999-04-23 10:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Tom Lane wrote:
>
> SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC;
>
> might do the right thing. It *seems* to select the first row for each
> value of place. I've never seen a spec for this feature, however, so
> I'm not sure if it's reliable or not...
Works a treat when I try it on the "real" database (using 6.4.2). It seems
very efficient. I didn't realise that "DISTINCT ON" worked that way with an
"ORDER BY".
I must say I'm overwhelmed by the variety of constructive responses to my
request. Thank you all very much. It beats the hell out of sitting as no 17
in a premium rate telephone queue for support from a vendor of a "commercial"
product.
--
Julian Scarfe
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 14:42 Brook Milligan <brook@trillium.NMSU.Edu>
parent: Julian Scarfe <jas1@scigen.co.uk>
0 siblings, 2 replies; 24+ messages in thread
From: Brook Milligan @ 1999-04-23 14:42 UTC (permalink / raw)
To: jas1@scigen.co.uk; +Cc: tgl@sss.pgh.pa.us; pgsql-sql
Tom Lane wrote:
>
> SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC;
>
> might do the right thing. It *seems* to select the first row for each
> value of place. I've never seen a spec for this feature, however, so
> I'm not sure if it's reliable or not...
Cool idea. Is DISTINCT guarranteed to choose the first row that
matches or can it choose any row?
Cheers,
Brook
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 16:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 1999-04-23 16:06 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; +Cc: pgsql-sql; pgsql-hackers@postgreSQL.org
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
>>>>>>> select title, summary, time from story t where time = (select
>>>>>>> max(s.time) from story s GROUP BY s.title);
>>
>>>> Why doesn't replacing "=" with "IN" produce a result? It wouldn't be the
>>>> desired result, but I thought this was legal.
>>
>> I thought so too (on both counts). Are you saying it doesn't work?
>> What happens? Which version are you using?
> httpd=> select title, summary, time from story t where time IN (select
> max(s.time) from story s GROUP BY s.title);
> ERROR: parser: Subselect has too many or too few fields.
> I'm using postgresql-snap-990329.tgz
Yeah, I see it too. This looks like a definite bug to me, but I have
other bugs to squash right now :-(. Anyone else want to jump on this?
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 16:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brook Milligan <brook@trillium.NMSU.Edu>
1 sibling, 0 replies; 24+ messages in thread
From: Tom Lane @ 1999-04-23 16:29 UTC (permalink / raw)
To: Brook Milligan <brook@trillium.NMSU.Edu>; +Cc: pgsql-sql
Brook Milligan <brook@trillium.NMSU.Edu> writes:
> Tom Lane wrote:
>>
>> SELECT DISTINCT ON place * FROM table ORDER BY place, time DESC;
>>
>> might do the right thing. It *seems* to select the first row for each
>> value of place. I've never seen a spec for this feature, however, so
>> I'm not sure if it's reliable or not...
> Cool idea. Is DISTINCT guarranteed to choose the first row that
> matches or can it choose any row?
I dunno, that's why I said I was unsure that this method was reliable.
By experimentation it seems that Postgres' DISTINCT code works that way,
but I have no idea whether the SQL spec mandates it or allows any row
within a group to be chosen.
I recall now that when I first heard of "SELECT DISTINCT ON field"
I objected that the results weren't well-defined (since it's not clear
how DISTINCT will choose which tuple to return). It might be that the
SQL spec requires the first tuple to be chosen for each value of
"field", which would allow the user to control the results by inserting
a preceding ORDER BY step --- or skip the ORDER BY, if he doesn't really
care which tuple he gets. That'd actually be a pretty cool design.
Anyone have an SQL spec handy to check it?
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [HACKERS] Re: [SQL] Finding the "most recent" rows
@ 1999-04-23 19:43 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 24+ messages in thread
From: Bruce Momjian @ 1999-04-23 19:43 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: chris.bitmead@bigfoot.com; pgsql-sql; pgsql-hackers@postgreSQL.org
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> >>>>>>> select title, summary, time from story t where time = (select
> >>>>>>> max(s.time) from story s GROUP BY s.title);
> >>
> >>>> Why doesn't replacing "=" with "IN" produce a result? It wouldn't be the
> >>>> desired result, but I thought this was legal.
> >>
> >> I thought so too (on both counts). Are you saying it doesn't work?
> >> What happens? Which version are you using?
>
> > httpd=> select title, summary, time from story t where time IN (select
> > max(s.time) from story s GROUP BY s.title);
> > ERROR: parser: Subselect has too many or too few fields.
This is not legal. If you use GROUP BY, the field must be in the target
list. In this case, s.title is not in the target list of the subselect.
I realize it can't be in the subselect target list because you can only
have one column in the target list, but that is the case.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-29 10:04 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 24+ messages in thread
From: Herouth Maoz @ 1999-04-29 10:04 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; pgsql-sql
At 07:39 +0300 on 23/04/1999, Chris Bitmead wrote:
> httpd=> select title, summary, time from story t where time IN (select
> max(s.time) from story s GROUP BY s.title);
> ERROR: parser: Subselect has too many or too few fields.
Of course it does. Since you group by a field which is not in the select
list, Postgres adds it silently to the fields to be selected. Thus the
subselect has two fields in each row, not just a single time field.
I think the proper syntax by the standard would be something like:
SELECT title, summary, time
FROM story t
WHERE (time, title) IN (
SELECT s.title, max( s.time )
FROM story s
GROUP BY s.title
);
But I'm not sure Postgres even supports this format (of comparing against
several fields).
In any case, the best would be to select just one tuple in the subselect
and have it return only the time, by constraining the title. But
syntactically, the above is what you were trying to do.
Anyway, Postgres adds the group field to the query, which the standard
requires and common practice doesn't. I think perhaps after doing the
grouping, Postgres should drop that field, since it wasn't originally
requested.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-29 10:28 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Brook Milligan <brook@trillium.NMSU.Edu>
1 sibling, 2 replies; 24+ messages in thread
From: Herouth Maoz @ 1999-04-29 10:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
At 19:29 +0300 on 23/04/1999, Tom Lane wrote:
> I recall now that when I first heard of "SELECT DISTINCT ON field"
> I objected that the results weren't well-defined (since it's not clear
> how DISTINCT will choose which tuple to return). It might be that the
> SQL spec requires the first tuple to be chosen for each value of
> "field", which would allow the user to control the results by inserting
> a preceding ORDER BY step --- or skip the ORDER BY, if he doesn't really
> care which tuple he gets. That'd actually be a pretty cool design.
> Anyone have an SQL spec handy to check it?
DISTINCT ON is a non-standard structure. Your initial assertion, that it
will return different results in different implementations holds true.
Standard SQL should return the same result sets (order not important unless
ORDER BY was mentioned).
Tables are considered as unordered sets. There can not be anything in the
standard that relies on the order in the table. And what do you mean by
inserting a preceding ORDER BY step?
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-29 14:28 Chris Bitmead <chris.bitmead@bigfoot.com>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 24+ messages in thread
From: Chris Bitmead @ 1999-04-29 14:28 UTC (permalink / raw)
To: pgsql-sql
Herouth Maoz wrote:
> Tables are considered as unordered sets. There can not be anything in the
> standard that relies on the order in the table. And what do you mean by
> inserting a preceding ORDER BY step?
He was suggesting that an ORDER BY combined with a DISTINCT might be a
good way of solving the difficult problem of finding the, say,
max(field) in different groups in the table. (And it does work for
postgresql) Like a SELECT ...GROUP BY except getting back not just the
group fields but all fields.
You say that a table is "considered an unordered set". But surely a
table is not an unordered set if you've specified an ORDER BY clause?
This idea is so nice, it would be ashame to dismiss it too quickly.
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: [SQL] Finding the "most recent" rows
@ 1999-04-29 15:03 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 24+ messages in thread
From: Herouth Maoz @ 1999-04-29 15:03 UTC (permalink / raw)
To: Chris Bitmead <chris.bitmead@bigfoot.com>; pgsql-sql
At 17:28 +0300 on 29/04/1999, Chris Bitmead wrote:
>
> You say that a table is "considered an unordered set". But surely a
> table is not an unordered set if you've specified an ORDER BY clause?
> This idea is so nice, it would be ashame to dismiss it too quickly.
Charming, but alas, non standard. Moreover, what if you want to get the
maximal value, but order in an ascending in the output?
No, if you want compatible SQL, you won't use DISTINCT ON, and you will
specify the exact method of picking the distinguished value.
Furthermore, ORDER BY has to be a late stage, if it should work on the
entire results of unions, for example, or the result of groups.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~1999-04-29 15:03 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-22 14:14 Finding the "most recent" rows Julian Scarfe <jas1@scigen.co.uk>
1999-04-22 14:45 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-22 15:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-22 23:57 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-23 00:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-23 02:30 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-23 04:08 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-23 04:39 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-23 16:06 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-23 19:43 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-04-29 10:04 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-04-23 10:30 ` Julian Scarfe <jas1@scigen.co.uk>
1999-04-23 14:42 ` Brook Milligan <brook@trillium.NMSU.Edu>
1999-04-23 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-29 10:28 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-04-29 14:28 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-29 15:03 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-04-22 14:46 ` George Moga <george@flex.ro>
1999-04-22 18:51 ` Brook Milligan <brook@trillium.NMSU.Edu>
1999-04-23 08:30 ` Chairudin Sentosa <chairudin@prima.net.id>
1999-04-23 04:10 ` Brook Milligan <brook@trillium.NMSU.Edu>
1999-04-22 14:53 RE: [SQL] Finding the "most recent" rows Michael J Davis <michael.j.davis@tvguide.com>
1999-04-23 08:34 ` Chairudin Sentosa <chairudin@prima.net.id>
1999-04-23 02:00 ` Chris Bitmead <chris.bitmead@bigfoot.com>
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