Most Recent Event Based on Date

Question

I have a project I am working on and I need to extract the most recent date from each each equipment number. I can't get MAX() to function with this. Please see the following code. The idea is that it goes through the table and pulls each row and then compares the event_date for the most recent date for each eq_num.

SELECT e.event_id, 
    l.location, 
    e.event_date, 
    c.name,
    q.eq_type, 
    e.pickup_date,
    e.eq_to_c AS eq_num,
    e.eq_from_c,
    CASE 
        WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c != e.eq_from_c THEN 'Out'
        WHEN e.eq_from_c = '' THEN l.location 
        WHEN e.eq_from_c IS NULL OR e.eq_from_c = '' THEN l.location
        WHEN e.eq_to_c = e.eq_from_c THEN 'Returned'
        WHEN e.eq_to_c != e.eq_from_c THEN l.location
        WHEN l.location = '' OR l.location IS NULL AND e.eq_to_c = e.eq_from_c THEN 'Returned'
    END AS eq_status,
    CASE
        WHEN CAST(e.event_date AS DATE) < CAST(e.pickup_date AS DATE) THEN e.pickup_date
        WHEN CAST(e.pickup_date AS DATE) < CAST(e.event_date AS DATE) THEN e.event_date
        WHEN CAST(e.event_date AS DATE) = CAST(e.pickup_date AS DATE) THEN e.event_date
    END AS event_date
    FROM events e 
        LEFT JOIN location l ON (e.location_id = l.id)
        LEFT JOIN clients c ON (e.event_client_id = c.id)
        LEFT JOIN equip_type q ON (e.req_eq_type_id = q.eq_type_id)
        ORDER BY eq_num, event_date DESC

Answer 1

Why can't you use the MAX() function?

I did the following

mysql> CREATE TABLE fred (myd date);
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO fred VALUES('2015-10-05');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO fred VALUES('2014-10-05');
Query OK, 1 row affected (0.07 sec)

mysql> SELECT MAX(myd) FROM fred;
+------------+
| MAX(myd)   |
+------------+
| 2015-10-05 |
+------------+
1 row in set (0.00 sec)

MAX(myd) works perfectly. Could you show where "I can't get MAX() to function with this." is failing, along with the error message?