MYSQL - Group BY with MAX issue

Question

I have the following table

  Date, TelephoneNumber, Type
  02/02/12, 123456, b
  04/02/12, 123456, b
  07/02/12, 123456, a
  03/02/12, 789999, a
  15/02/12, 789999, b

When running the following SQL

        select TelephoneNumber, max(Date) as datetime, Type
        from Table1
        where Date > '2012-03-25 00:00'
        group by TelephoneNumber
        order by date desc;

I noted that the Type does not match its related Date. For example I am getting

  07/02/12, 123456, b
  15/02/12, 789999, a

It seems that it is taking the first record in Type .... even when I sorted in the other way round. Can someone please help me how I can solve this problem? I am using MySQL

Thanks in advance. sandro

MySQL's group-by function uses the first-encountered row to fill in a value for any non-grouped field in a result row. This is unavoidable with simple query statments like yours. See this Q&A: http://stackoverflow.com/questions/979034/mysql-shows-incorrect-rows-when-using-group-by — Marc B, Mar 26 '12 at 20:48
What is it exactly that you are expecting to happen. Asking a more direct question is always better. — kasavbere, Mar 26 '12 at 21:00

score 3 · Accepted Answer · edited May 23 '17 at 12:29

3

This question seems to pop up quite often.

Here's my solution:

SELECT TelephoneNumber, Date AS datetime, Type
from ( SELECT *
       FROM Table1
       WHERE Date > '2012-03-25 00:00'
       ORDER BY Date DESC) AS h
GROUP BY TelephoneNumber
ORDER BY date DESC;

Check explanation here

edited May 23 '17 at 12:29

Community

1
1

answered Mar 26 '12 at 21:13

Robin Castlin

10,956
1
28
44

score 2 · Answer 2 · answered Mar 26 '12 at 20:54

This is because, as §11.16.3 "GROUP BY and HAVING with Hidden Columns" in the MySQL 5.6 Reference Manual puts it:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

[emphasis mine]

Instead, you need to write something like this:

select t1a.TelephoneNumber, t1a.Date, t1a.Type
  from Table1 as t1a
  left
  join Table1 as t1b
    on t1b.TelephoneNumber = t1a.TelephoneNumber
   and t1b.Date > t1a.Date
 where t1a.Date > '2012-03-25 00:00'
   and t1b.TelephoneNumber IS NULL                -- i.e., the join failed
;

to find the record with the greatest Date for each value of TelephoneNumber.

score 0 · Answer 3 · answered Mar 26 '12 at 20:50

0

Order is applied after grouping. The value of max(Date) has already been computed, you're not sorting it in any way. Also, what is msisdn? It's impossible to tell what's going on with your query because what you're actually grouping on is opaque.

answered Mar 26 '12 at 20:50

KernelM

8,776
2
23
16

that was a mispprint :) check now please – mouthpiec Mar 26 '12 at 20:52

MYSQL - Group BY with MAX issue

3 Answers3