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

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 (view raw or flat)
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

pgsql-sql by date

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

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