Skip site navigation (1) Skip section navigation (2)

Re: problem query ...

From: John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, John Taylor <postgres(at)jtresponse(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: problem query ...
Date: 2002-01-31 17:08:29
Message-ID: 02013117082906.01674@splash.hq.jtresponse.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
On Thursday 31 January 2002 17:02, Tom Lane wrote:
> John Taylor <postgres(at)jtresponse(dot)co(dot)uk> writes:
> > I want to return 1 row for each id, that contains the maximum update
> > value for that id, and the values for name and desc.
> 
> There's no simple way to do that in standard SQL.  However you can do
> it easily with SELECT DISTINCT ON, if you don't mind using a nonstandard
> construct.  See the "weather report" example on the SELECT reference
> page.

That was it!
I did try distinct on before, but was trying to be too complicated, with group or subselects.
What I wanted is:

select distinct on (id)  id,name,descr from john order by id,update desc; 

Thanks
JohnT

In response to

pgsql-novice by date

Next:From: Frank BaxDate: 2002-01-31 17:35:40
Subject: Re: problem query ...
Previous:From: Tom LaneDate: 2002-01-31 17:02:21
Subject: Re: problem query ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group