How can I get this database to order before the GROUP BY

Question

I made a website for golf scorecards. The page I am working on is the players profile. When you access a players profile, it shows each course in order of last played (DESC). Except, the order of last played is jumbled due to the ORDER BY command below. Instead, when it GROUPs, it takes the earliest date, rather than the most recent.

After the grouping is done, it correctly shows them in order (DESC)... just the wrong order due to the courses grouping by date_of_game ASC, rather than DESC. Hope this isn't too confusing.. Thank you.

$query_patrol321 = "SELECT t1.*,t2.* FROM games t1 LEFT JOIN scorecards t2 ON t1.game_id=t2.game_id WHERE t2.player_id='$player_id' GROUP BY t1.course_id ORDER BY t1.date_of_game DESC";
$result_patrol321 = mysql_query($query_patrol321) or die ("<br /><br />There's an error in the MySQL-query: ".mysql_error());
while ($row_patrol321 = mysql_fetch_array($result_patrol321)) {
    $player_id_rank = $row_patrol321["player_id"];
    $course_id = $row_patrol321["course_id"];
    $game_id = $row_patrol321["game_id"];
    $top_score = $row_patrol321["total_score"];

I don't understand the question fully, but can you try changing the ordering to `ORDER BY MAX(t1.date_of_game) DESC`? That will work if you're trying to show the last play date for each course. — Ed Gibbs, May 01 '13 at 17:16
Perfect, thank you. Glad it was such an easy fix. I couldn't figure it out. — Rmurp006, May 01 '13 at 17:21
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, May 01 '13 at 18:07

score 2 · Answer 1 · answered May 01 '13 at 17:22

2

Try to remove the GROUP BY-clause from the query. You should use GROUP BY only when you have both normal columns and aggregate functions (min, max, sum, avg, count) in your SELECT. You have just normal columns.

answered May 01 '13 at 17:22

slaakso

8,331
2
16
27

+1, `GROUP BY` without aggregation is a misuse of SQL. – Adrian May 01 '13 at 17:35
MySQL allows this behavior. I've always found it strange and confusing. I believe without an aggregate it takes the first entry in the aggregation. – Matthew May 01 '13 at 17:39
While it is true that MySQL does not raise an error for using GROUP BY without an aggregate function + normal column, you really should not use it this way. There is always correct way of doing the query. – slaakso May 01 '13 at 20:33

Matthew · Answer 2 · 2013-05-01T17:38:40.863

The fact that it shows the grouping result in ASC order is a coincidence because that is the order of their insertion. In contrast to other RDBMS like MS SQL Server, MySQL allows you to add non-aggregated columns to a GROUPed query. This non-standard behavior creates the confusion you're seeing. If this were not MySQL, you'd need to define the aggregation for all your selected columns given the grouping.

MySQL's behavior is (I believe) to take the first row matching the the GROUP for non-aggregated columns. I would advise against doing this.

Even though you're aggregating, you're not ORDERing by the aggregated column.

So What you want to do is ORDER BY the MAX date DESC

In this way, you are ordering by the latest date per course (your grouping criteria).

SELECT 
    t1.* -- It would be better if you actually listed the aggregations you wanted
    ,t2.* -- Which columns do you really want?
FROM 
    games t1 
LEFT JOIN 
    scorecards t2 
        ON t2.[game_id] =t1[.game_id]
WHERE 
    t2.[player_id]='$player_id' 
GROUP BY 
    t1.[course_id] 
ORDER BY 
    MAX(t1.[date_of_game]) DESC

score 0 · Answer 3 · answered May 01 '13 at 18:04

If you want the maximum date, then insert logic to get it. Don't depend on the ordering of columns or on undocumented MySQL features. MySQL explicitly discourages the use of non-aggregated columns in the group by when the values are not identical:

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.  (see [here][1])

How do you do what you want? The following query finds the most recent date on each course and just uses that -- and no group by:

SELECT t1.*, t2.*
FROM games t1 LEFT JOIN
     scorecards t2
     ON t1.game_id=t2.game_id
WHERE t2.player_id='$player_id' and
      t1.date_of_game in (select MAX(date_of_game)
                          from games g join
                               scorecards ss
                               on g.game_id = ss.game_id and
                                  ss.player_id = '$player_id'
                          where t1.course_id = g.course_id
                         )
GROUP BY t1.course_id
ORDER BY t1.date_of_game DESC

If game_id is auto incrementing, you can use that instead of date_of_game. This is particularly important if two games can be on the same course on the same date.

How can I get this database to order before the GROUP BY

3 Answers3