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
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;
