MAX() Function not working as expected

Question

I've created sqlfiddle to try and get my head around this http://sqlfiddle.com/#!2/21e72/1

In the query, I have put a max() on the compiled_date column but the recommendation column is still coming through incorrect - I'm assuming that a select statement will need to be inserted on line 3 somehow?

I've tried the examples provided by the commenters below but I think I just need to understand this from a basic query to begin with.

Are your dates stored using a DATE data type? Also, consider providing proper DDLs and/or an sqlfiddle TOGETHER WITH THE DESIRED RESULT SET — Strawberry, Jan 07 '14 at 11:34
This only produces a result due to mysql's laxness about `GROUP BY` and aggregate functions. Consider if you had another column that you wanted to obtain the `MAX()` value for, and as it happens, the `MAX` for that column occurs in *different* rows to the rows for which `MAX(compiled_date)` are found (or even that there could be *multiple* rows which have the same, maximal, `compiled_date` value). There are now *multiple* possible values for `recommendation` and mysql just selects one. — Damien_The_Unbeliever, Jan 07 '14 at 11:34
Your query contains fields in the SELECT clause which are not part of the GROUP BY clause. [In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause](http://dev.mysql.com/doc/refman/5.5/en/group-by-extensions.html). However, mysql allows it which may produce unexpected results in some cases. See this post for more details: http://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by — Aziz Shaikh, Jan 07 '14 at 11:40

score 5 · Accepted Answer · answered Jan 07 '14 at 15:08

As others have pointed out, the issue is that some of the select columns are neither aggregated nor used in the group by clause. Most DBMSs won't allow this at all, but MySQL is a little relaxed on some of the standards...

So, you need to first find the max(compiled_date) for each case, then find the recommendation that goes with it.

select r.case_number, r.compiled_date, r.recommendation
from reporting r
  join (
    SELECT case_number, max(compiled_date) as lastDate
    from reporting
    group by case_number
  ) s on r.case_number=s.case_number
      and r.compiled_date=s.lastDate

i did eventually get there on my own i just need to get a few more table joins in and im good to go but thank you for confirming felt like knock my head against a wall today :) — Will, Jan 07 '14 at 19:17

score 3 · Answer 2 · answered Jan 07 '14 at 14:36

Thank you for providing sqlFiddle. But only reporting data is given. we highly appreciate if you give us sample data of whole tables.

Anyway, Could you try this?

SELECT
    `case`.number,
    staff.staff_name AS ``case` owner`,
    client.client_name,
    `case`.address,
    x.mx_date,
    report.recommendation
FROM
    `case` INNER JOIN (
        SELECT case_number, MAX(compiled_date) as mx_date
        FROM report
        GROUP BY case_number
    ) x ON x.case_number = `case`.number
    INNER JOIN report ON x.case_number = report.case_number AND report.compiled_date = x.mx_date
    INNER JOIN client ON `case`.client_number = client.client_number
    INNER JOIN staff ON `case`.staff_number = staff.staff_number
WHERE
    `case`.active = 1
    AND staff.staff_name = 'bob'
ORDER BY
    `case`.number ASC;

Saharsh Shah · Answer 3 · 2014-01-07T11:45:28.510

Check below query:

SELECT c.number, s.staff_name AS `case owner`, cl.client_name, 
       c.address, MAX(r.compiled_date), r.recommendation
FROM case c
INNER JOIN (SELECT r.case_number, r.compiled_date, r.recommendation 
            FROM report r ORDER BY r.case_number, r.compiled_date DESC
           ) r ON r.case_number = c.number
INNER JOIN client cl ON c.client_number = cl.client_number
INNER JOIN staff s ON c.staff_number = s.staff_number
WHERE c.active = 1 AND s.staff_name = 'bob'
GROUP BY c.number
ORDER BY c.number ASC

The Hungry Dictator · Answer 4 · 2014-01-07T11:55:58.430

-1

SELECT
case.number,
staff.staff_name AS `case owner`,
client.client_name,
case.address,
(select MAX(compiled_date)from report where case_number=case.number),
report.recommendation
FROM
case
INNER JOIN report ON report.case_number = case.number
INNER JOIN client ON case.client_number = client.client_number
INNER JOIN staff ON case.staff_number = staff.staff_number
WHERE
case.active = 1 AND
staff.staff_name = 'bob'
GROUP BY
case.number
ORDER BY
case.number ASC

try this

edited Jan 07 '14 at 11:55

answered Jan 07 '14 at 11:36

The Hungry Dictator

3,444
5
37
53

error - Unknown column 'report.recommendation' in 'field list' (removing the report.recommendation line works but just leads me back to the original query i used above) – Will Jan 07 '14 at 11:48
[Err] 1241 - Operand should contain 1 column(s) (sorry if im being dumb!) – Will Jan 07 '14 at 11:54
it will pull through the information latest compiled date for each case – Will Jan 07 '14 at 11:59
its still pulling the old recommendation entry and the (select MAX(compiled_date)from report where case_number=case.number) is pulling "2103-05-14" – Will Jan 07 '14 at 12:10

MAX() Function not working as expected

4 Answers4