MySQL "Group By" and "Order By"

A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:

SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`)

This is similar to using the join but looks much nicer. Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones. IMPORTANT UPDATE Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "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."

Get Latest Record In Each MySQL Group | How to select the first/least/max row per group in SQL | Select max, min, last row for each group in SQL without a subquery | MySQL - How To Get Top N Rows per Each Group

First approach (LEFT JOIN)

---

SELECT s.Name,c1.Id AS Max_Score_ID,c1.Score as Max_Score
FROM Students s
LEFT JOIN Scores c1 ON (c1.Student=s.Id)
LEFT JOIN Scores c2 ON (
  c1.Student=c2.Student 
  AND (c1.Score<c2.Score OR (c1.Score=c2.Score AND c1.Id<c2.Id))
)
WHERE c2.Score IS NULL
ORDER BY c1.Score DESC;

Execution Plan: First image shows statistics, an index is used when performing the above SQL, example created in MySQL-Fiddle. And second image for large data size, its around 8 million rows, and it takes a small amount of time to execute.

---

Now we will go for second approach (GROUP_CONCAT WITH SUBSTRING_INDEX)

---

SELECT s.Name,
CAST(SUBSTRING_INDEX(
  GROUP_CONCAT(c1.Id order by c1.Score desc),',',1
) AS DECIMAL(10, 2)) AS Max_Score_ID,
CAST(SUBSTRING_INDEX(
  GROUP_CONCAT(c1.Score order by c1.Score desc),',',1
) AS DECIMAL(10, 2)) as Max_Score
FROM Students s
LEFT JOIN Scores c1 ON (c1.Student=s.Id)
GROUP BY s.Id
ORDER BY Max_Score DESC;

Execution plan: First image show statistics taken from MySQL-Fiddle, used group-concat and substring-index both to calculate (min/max) value and/or other column of the table. Second image show that its also good time effective as it also takes a small amount of time to get min/max value and table size around 8 million rows.

---

MySQL Fiddle Link

Get Latest Record In Each MySQL Group

Table=check_group_by
id   group_by    value
-------------------------
1    1           Value 1
2    1           Value 2
3    1           Value 3
4    2           Value 4
5    2           Value 5
6    3           Value 6

Query will be as like:

SELECT id,group_by,value FROM check_group_by
WHERE id IN (
 SELECT MAX(id) FROM check_group_by GROUP BY group_by
)

SQL Fiddle Example