Max Date not working as expected

Question

I have a table that I'm trying to filter data on.

+-------------+-------------+------------+------------+---------------------+
| mod_coli_id | line_number | total_cost | total_sell | modify_date         |
+-------------+-------------+------------+------------+---------------------+
|     1403974 |           1 |   544.5000 |   640.5900 | 2016-09-13 13:21:15 |
|     1403976 |           1 |   544.5000 |   640.5900 | 2016-09-13 13:26:54 |
|     1403986 |           1 |   544.5000 |   544.5000 | 2016-09-13 13:35:49 |
|     1405972 |           1 |   544.5000 |   544.5000 | 2016-09-15 10:02:11 |
+-------------+-------------+------------+------------+---------------------+

When I run the following query,

SELECT mod_coli_id,line_number,total_cost,total_sell,max(modify_date) as modify_date
From mod_customer_order_line_item
Where customer_order_id=17761 AND modify_date<='2016-09-14'

The result I get is this.

+-------------+-------------+------------+------------+---------------------+
| mod_coli_id | line_number | total_cost | total_sell | modify_date         |
+-------------+-------------+------------+------------+---------------------+
|     1403974 |           1 |   544.5000 |   640.5900 | 2016-09-13 13:35:49 |
+-------------+-------------+------------+------------+---------------------+

Seems like MySQL is returning the first result (mod_coli_id of 1403974) and the max modify_date.

The result that I'm trying to get would be the oldest modify date for each line number (as there could be multiple line_numbers). Eg.

+-------------+-------------+------------+------------+---------------------+
| mod_coli_id | line_number | total_cost | total_sell | modify_date         |
+-------------+-------------+------------+------------+---------------------+
|     1403986 |           1 |   544.5000 |   544.5000 | 2016-09-13 13:35:49 |
+-------------+-------------+------------+------------+---------------------+

Any thoughts?

EDIT: Clarified the result I'm looking for.

Answer 1

Yes, this is expected behaviour when you are using the non-standard MySQL "feature" of unorthodox GROUP BY. See this related post at SO: Why does MySQL add a feature that conflicts with SQL standards?

Your query basically says to the engine that you want the maximum date and arbitrary values for the other columns. And MySQL does that, exactly. It gets you the maximum date and arbitrary values for the other columns. You should consider yourself lucky that the values came form the same row. They might have been from different rows as well!

Now, to solve your issue, don't use this feature - unless you know very well what you are doing!. Here is another way to get the expected results. Don't use MAX() in the SELECT - which does an implicit GROUP BY the whole set - but do an ORDER BY date DESC and then LIMIT 1 to get only the row with the highest date:

SELECT mod_coli_id, line_number,total_ cost,total_sell, modify_date
FROM mod_customer_order_line_item
WHERE customer_order_id = 17761 
  AND modify_date <= '2016-09-14'
ORDER BY modify_date DESC
LIMIT 1 ;