How to use maximum of date function in db2

dinesh1985 · #1 (**permalink**) 06-06-11, 14:28

Hello Folks,
I am having a below example

Code:

CAR_NAME   SERVICE_DATE
------------------------------------
ALTIMA        01/01/2007
ALTIMA        03/01/2007
ALTIMA        05/02/2008
FORD GT      01/01/2009
FORD GT      01/01/2010
FORD GT      01/06/2010
FORD GT      01/01/2011

In the above example, CARs and their servicing dates are stored in the table. Altima has done three services in which the latest service date is done on 05/02/2008 and FORD GT has done the latest service on 01/01/2011.

I need to write a db2 query to fetch the vehicles for which the latest service is done before Dec 2008. For the Above example, the expected result is ALTIMA.

I am trying something like this

Code:

SELECT DISTINCT CAR_NAME FROM CAR_SERVICE_TABLE WHERE MAX(SERVICE_DATE) < '12/31/2008' WITH UR

But this is not working.

I am getting the error as
SQL0120N invalid use of an aggregate function or OLAP function. SQLSTATE = 42903.

Please help me in writing the query