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

Thanks Bart,

Your explanation is great. Returned and Bought are of datatypes date, hence
Returned would be a more recent value.

On Fri, Sep 12, 2008 at 12:10 AM, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be>wrote:

> 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.
>
>
> Model Bought Returned X 2004-08-25 2005-01-01 -> Greatest = 2005-01-01
> X 2006-02-17 2006-02-18 -> Greatest = 2006-02-18 X 2005-11-13 2001-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 >><rubes7202(at)gmail(dot)com%3E+2008-09-11+19%3A33+%3E%3E>
> 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;
>
>
>