pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAntw: using max() aggregate
4+ messages / 2 participants
[nested] [flat]
* Antw: using max() aggregate
@ 2000-06-16 07:28 Gerhard Dieringer <DieringG@eba-haus.de>
0 siblings, 0 replies; 4+ messages in thread
From: Gerhard Dieringer @ 2000-06-16 07:28 UTC (permalink / raw)
To: pgsql-sql; +Cc: cunctator@apartia.ch
Louis-David Mitterrand - ldm@apartia.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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Antw: using max() aggregate
@ 2000-06-16 08:11 Louis-David Mitterrand <cunctator@apartia.ch>
0 siblings, 0 replies; 4+ messages in thread
From: Louis-David Mitterrand @ 2000-06-16 08:11 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: pgsql-sql
On Fri, Jun 16, 2000 at 09:28:27AM +0200, Gerhard Dieringer wrote:
> > 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.
>
> SELECT title,stopdate
> FROM auction
> WHERE stopdate = (SELECT max(stopdate) FROM auction);
>
> should work.
Thanks for your suggestion.
Yes this would work nicely but if I need to add more conditional clauses
I have to duplicate them in the main SELECT and in the sub-SELECT:
SELECT title,max(stopdate)
FROM auction
WHERE stopdate = (SELECT max(stopdate) FROM auction AND stopdate > now())
AND stopdate > now();
Or am I missing something?
Tom Lane suggested using:
SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1;
which seems the best solution (I was a bit concerned about performance,
but then again the max() aggregate does a scan of all rows as well).
Cheers,
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr
This is Linux Country. On a quiet night you can hear Windows NT reboot.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Antw: using max() aggregate
@ 2000-06-16 08:56 Gerhard Dieringer <DieringG@eba-haus.de>
2000-06-16 10:54 ` Re: Antw: using max() aggregate Louis-David Mitterrand <cunctator@apartia.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Gerhard Dieringer @ 2000-06-16 08:56 UTC (permalink / raw)
To: cunctator@apartia.ch; +Cc: " <"<pgsql-sql@postgresql.org>
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr wrote:
> ...
> Yes this would work nicely but if I need to add more conditional clauses
> I have to duplicate them in the main SELECT and in the sub-SELECT:
>
> SELECT title,max(stopdate)
> FROM auction
> WHERE stopdate = (SELECT max(stopdate) FROM auction AND stopdate > now())
> AND stopdate > now();
>
> Or am I missing something?
>
> Tom Lane suggested using:
>
> SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1;
>
> which seems the best solution (I was a bit concerned about performance,
> but then again the max() aggregate does a scan of all rows as well).
> ...
I don't see why you repeat your conditions in the outer select. The condition in the inner select drops all records that violate the conditions,
so the same conditions in the outer select have nothing to do and you can leave them away.
Tom's solution has the drawback, that if you have more than one record with the same max value you only get one of them, but may be that you want
to see all of them.
Gerhard
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Antw: using max() aggregate
2000-06-16 08:56 Re: Antw: using max() aggregate Gerhard Dieringer <DieringG@eba-haus.de>
@ 2000-06-16 10:54 ` Louis-David Mitterrand <cunctator@apartia.ch>
0 siblings, 0 replies; 4+ messages in thread
From: Louis-David Mitterrand @ 2000-06-16 10:54 UTC (permalink / raw)
To: Gerhard Dieringer <DieringG@eba-haus.de>; +Cc: " <" <pgsql-sql@postgresql.org>
On Fri, Jun 16, 2000 at 10:56:04AM +0200, Gerhard Dieringer wrote:
> > Yes this would work nicely but if I need to add more conditional clauses
> > I have to duplicate them in the main SELECT and in the sub-SELECT:
> >
> > SELECT title,stopdate
> > FROM auction
> > WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE stopdate > now())
> > AND stopdate > now();
> >
> > Or am I missing something?
> >
> > Tom Lane suggested using:
> >
> > SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1;
> >
> > which seems the best solution (I was a bit concerned about performance,
> > but then again the max() aggregate does a scan of all rows as well).
> > ...
>
> I don't see why you repeat your conditions in the outer select. The
> condition in the inner select drops all records that violate the
> conditions, so the same conditions in the outer select have nothing to
> do and you can leave them away.
Maybe mine was a bad example but if, for instance, you add a condition
on the "login" attribute (that it should start with a 'm'), then if you
omit the clause from the outer select you risk having a false match if
two records have the same stopdate:
SELECT title,login,stopdate
FROM auction
WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE login LIKE 'm%');
> Tom's solution has the drawback, that if you have more than one record
> with the same max value you only get one of them, but may be that you
> want to see all of them.
True.
Thanks,
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr
Save the whales. Feed the hungry. Free the mallocs.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-06-16 10:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-16 07:28 Antw: using max() aggregate Gerhard Dieringer <DieringG@eba-haus.de>
2000-06-16 08:11 Re: Antw: using max() aggregate Louis-David Mitterrand <cunctator@apartia.ch>
2000-06-16 08:56 Re: Antw: using max() aggregate Gerhard Dieringer <DieringG@eba-haus.de>
2000-06-16 10:54 ` Louis-David Mitterrand <cunctator@apartia.ch>
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