Thread: using max() aggregate

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,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

"Logiciels libres : nourris au code source sans farine animale."

Louis-David Mitterrand <cunctator@apartia.ch> writes:
> 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:

Perhaps

select * from auction order by stopdate desc limit 1;

In 7.0 this should even be pretty quick, if you have an index on
stopdate.

            regards, tom lane

On Fri, Jun 16, 2000 at 02:05:53AM -0400, Tom Lane wrote:
> Louis-David Mitterrand <cunctator@apartia.ch> writes:
> > 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:
>
> Perhaps
>
> select * from auction order by stopdate desc limit 1;
>
> In 7.0 this should even be pretty quick, if you have an index on
> stopdate.

Thanks, yours seems to be the best solution.

The performance hit between max() and ORDER BY should be roughly
comparable?

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

Louis-David Mitterrand <cunctator@apartia.ch> writes:
> On Fri, Jun 16, 2000 at 02:05:53AM -0400, Tom Lane wrote:
>> Perhaps
>> select * from auction order by stopdate desc limit 1;
>>
>> In 7.0 this should even be pretty quick, if you have an index on
>> stopdate.

> Thanks, yours seems to be the best solution.

> The performance hit between max() and ORDER BY should be roughly
> comparable?

No!  That's why I muttered about an index.  max() has to scan the
whole table (at least in its current incarnation).  ORDER BY with
LIMIT should be implemented as an indexscan that's only run for
one tuple --- in other words, the system basically reaches into
the index, pulls out the last entry, and you're done.

OTOH, if you don't have an index, then the ORDER BY has to be
implemented as a sequential scan followed by sort, which will surely
be slower than just a sequential scan --- for a large table it
will lose even compared to two sequential scans, which is what
you're really looking at for the subselect-based versions.

Either way, the performance is not very comparable...

BTW you need to be running 7.0.* to get the smart plan for
ORDER BY + LIMIT, the pre-7.0 optimizer would miss it in
many cases.

            regards, tom lane