[SOLVED] Help with Max dates

[tpimental]

I've been struggling with this query for way too long and decided I needed some help.  Seems simple enough, but I get 0 results.

 

I'm trying to get the latest date of a set of IDs (loc_id) in a report set only when that date is older than 1 year.

 

(mysql version 4.0.27)

 

SELECT Max( report_date ) AS LatestDate, loc_id

FROM report_index

WHERE 'LatestDate' < curdate( ) - INTERVAL 365 DAY

GROUP BY loc_id

LIMIT 0 , 500

 

Table looks something like this:

 

loc_id report_date

1 12/13/2001

2 3/4/2003

2 12/13/2001

3 6/4/2003

3 6/4/2003

4 3/6/2003

4 3/6/2003

5 3/14/2003

5 3/14/2003

5 9/8/2003

5 9/8/2003

 

 

 

[bubblegum.anarchy]

unquote LatestDate in the WHERE clause:

 

WHERE LatestDate < CURRENT_DATE - INTERVAL 1 YEAR

[tpimental]

Yah, I tried that.  Got this error:

 

#1054 - Unknown column 'LatestDate' in 'where clause'

[bubblegum.anarchy]

heh...

 

SELECT Max( report_date ) AS LatestDate, loc_id
FROM report_index
GROUP BY loc_id
HAVING max(report_date) < CURRENT_DATE - INTERVAL 1 YEAR
LIMIT 0 , 500

[tpimental]

That did it!  Thanks B.A.