Re: DIfference between max() and greatest() ?

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: "Ruben Gouveia" <rubes7202(at)gmail(dot)com>, "pgsql-sql" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: DIfference between max() and greatest() ?
Date: 2008-09-12 07:10:08
Message-ID: 48CA31F0.A3DD.0030.0@indicator.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

max(expression) is an aggregate function
(from the manual: "maximum value of expression across all input
values")
greatest(value [, ...]) is not an aggregate function
(from the manual: "The GREATEST function selects the largest value from
a list of any number of expressions.")

So max takes the maximum of values coming from several rows, while
greatest takes the maximum of values coming from 1 row.

Assuming
select model,count(distinct cars)
from rc_cars
group by model
returns more than one record you will need both functions.
Greatest gets the most recent date out of "bought" and "returned" PER
RECORD.
Max gets the most recent date out of all these greatest dates OVER ALL
RECORDS.

ModelBoughtReturned
X2004-08-252005-01-01->Greatest = 2005-01-01
X2006-02-172006-02-18->Greatest = 2006-02-18
X2005-11-132001-05-16->Greatest = 2005-11-13
¯
Max = 2006-02-18

That being said, isn't it unlikely that "bought" is more recent than
"returned"?
I can imagine that one can only return a car after buying it.
If so, writing
... WHERE max(returned) < current_date - interval '1 day' ...
would be enough.
Another thought: with this WHERE clause a car returned yesterday will
not show up.
Is that what you want? If not, use
... WHERE max(returned) < current_date ...

Good luck

>>> "Ruben Gouveia" rubes7202(at)gmail(dot)com> 2008-09-11 19:33 >> (
mailto:rubes7202(at)gmail(dot)com> )
What is the difference between these two. I know that max() is an
aggregate function

select model,count(distinct cars)
from rc_cars
where greatest(baught,returned) < current_date - integer '1'
group by model;

Do i need to have a max () around a greatest() to make sure i get the
most recent of the two.

select model,count(distinct cars)
from rc_cars
where max(greatest(baught,returned)) < current_date - integer '1'
group by model;

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Peter Eisentraut 2008-09-12 07:24:51 Re: DIfference between max() and greatest() ?
Previous Message Ruben Gouveia 2008-09-11 17:33:13 DIfference between max() and greatest() ?