0

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
0

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?

  • It's not that I can't get it to function as in it will not work, it just does not return the associated row with the date. It will give me the max date, but an older row of data. – akadianknight Oct 13 '15 at 2:34
  • mysql.rjweb.org/doc.php/groupwise_max shows how to get the associated row. – Rick James Oct 21 '15 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.