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 <[EMAIL PROTECTED]>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" [EMAIL PROTECTED]> 2008-09-11 19:33 >><[EMAIL PROTECTED]> > 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; > > >
