1

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.

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 ;
  • That clears up why the column data is not for that row. LIMIT 1 won't work if there's more than one line_number (which is often the case). How might I go about getting the last modify date for each line item? – Shadymilkman01 Sep 26 '16 at 17:29
  • Your query did not have GROUP BY. Did you intend to use GROUP BY line_number? – ypercubeᵀᴹ Sep 26 '16 at 17:30
  • Yes, but even with the GROUP BY clause it still returned arbitrary values for other columns. – Shadymilkman01 Sep 26 '16 at 17:32
  • I know it would. I was asking so I can update the answer. That's why it's important to have an accurate question in the first place! – ypercubeᵀᴹ Sep 26 '16 at 18:24
  • I see. Sorry about that. I think I found a solution that would work, would you say this query would be adequate? Select * From (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-16' Order By Modify_date DESC) a Group By line_number – Shadymilkman01 Sep 26 '16 at 19:17

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.