Обсуждение: RE: [SQL] Finding the "most recent" rows
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 AMTo: pgsql-sql@postgresql.orgSubject: Re: [SQL] Finding the "most recent" rows TrySELECT 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 Bitmeadhttp://www.bigfoot.com/~chris.bitmeadmailto:chris.bitmead@bigfoot.com
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
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