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

Antw: using max() aggregate

From: "Gerhard Dieringer" <DieringG(at)eba-haus(dot)de>
To: <pgsql-sql(at)postgresql(dot)org>
Cc: <cunctator(at)apartia(dot)ch>
Subject: Antw: using max() aggregate
Date: 2000-06-16 07:28:27
Message-ID: s949f34f.049@kopo001 (view raw or flat)
Thread:
Lists: pgsql-sql
Louis-David Mitterrand - ldm(at)apartia(dot)org - http://www.apartia.fr wrote:

> Hello,
> 
> I am trying to return the most recently updated record from a table:
>
> SELECT max(stopdate) FROM auction;
>
> and this works but only returns the timestamp, however if I try to get
> another column with the aggregate it fails:
> 
> SELECT title,max(stopdate) FROM auction;
> ERROR:  Attribute auction.title must be GROUPed or used in an aggregate function
> 
> Ok, so I group it now:
>
> SELECT title,max(stopdate) FROM auction GROUP BY title;
>      title     |          max           
> ---------------+------------------------
>  dfsdfsdf      | 2000-07-10 05:00:00+02
>  dssdfsdfsdfsf | 2000-07-09 16:00:00+02
>  sdfsdfsdfsdf  | 2001-04-10 15:00:00+02
> (3 rows)
> 
> But the problem is that I now get three rows when I only want the max()
> item.
> 
> How should I do it?
> 
> Thanks in advance,

I didn't test it, but something like

SELECT title,stopdate 
FROM auction
WHERE stopdate = (SELECT max(stopdate) FROM auction);

should work.

--------
Gerhard



pgsql-sql by date

Next:From: Louis-David MitterrandDate: 2000-06-16 08:11:21
Subject: Re: Antw: using max() aggregate
Previous:From: James CarpenterDate: 2000-06-16 06:41:14
Subject: Determining Array size

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