-1

I have a problem when use Order By and Group By in a query string.

There is a table careers that contains data as shown below:

id   se_id   enrollment_start
-------------------------
1    1       2005-07-01
2    2       2008-10-12
3    2       2006-05-09
4    1       2016-11-10
5    3       2015-02-04
6    3       2010-08-11

I want to get se_id which has highest enrollment_start.

This is a sql statement I used. It works in mysql 5.5 but not in mysql 5.7:

SELECT 
    tmp.*
FROM
    (SELECT 
        *
    FROM
        careers
    ORDER BY enrollment_start DESC) tmp
GROUP BY tmp.se_id

This is groupwise maximum problem, and there are many topics cover about it. But I don't want the answer for that problem, I want to know why above statement woking in mysql 5.5 but it doesn't work in mysql 5.7 and is there any method to fix it? Thank you.

Nghia Dau
  • 16
  • 2
  • if work in 5.5 was pure luck. that shouldnt work. – Juan Carlos Oropeza Oct 07 '16 at 04:15
  • I don't think so, I tested many times, it always work like a charm, even though I know subquerey returns unordered result – Nghia Dau Oct 07 '16 at 04:23
  • see the manual page https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Drew Oct 07 '16 at 04:32
  • @Drew: i set sql_mode = '' and still get nothing different – Nghia Dau Oct 07 '16 at 04:38
  • All I can say is write proper sql. See [this](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – Drew Oct 07 '16 at 04:51
  • and stay away from `select *` ... that is normally a disaster waiting to happen with any group by. Also pretty funny when done by people with unions and different tables (diff col counts). It is just sloppy programming. – Drew Oct 07 '16 at 05:28

1 Answers1

0

If you want to get se_id which has highest enrollment_start. use LIMIT:

SELECT  *
FROM    careers
ORDER BY enrollment_start DESC
LIMIT 1
gofr1
  • 15,741
  • 11
  • 42
  • 52
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118