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

MYSQL Group Concat Select Some Selected Rows Only | Use Sort In Group Concat | Sort MySQL Rows in Group Concat

Its so simple. Just need to do below thins:

SELECT SUBSTRING_INDEX(GROUP_CONCAT(x.id ORDER BY x.nx DESC), ',', 2) as row_name from some_table GROUP BY some_field

It will select First two values only.

It total value of GROUP_CONCAT is "1,2,3,4,5" Then Using SUBSTRING_INDEX would be like "1,2"

You can use DISTINCT in GROUP_CONCAT function like SUBSTRING_INDEX(DISTINCT(GROUP_CONCAT(x.id ORDER BY x.nx DESC)), ',', 2)

Mysql GROUP_CONCAT of first n rows || Selecting first and last values in a group || Select first and last row from group_concat when grouping sortable days

select s.id,count(e.id) as total,
substring_index(group_concat(e.id order by e.id asc),',',5) as group_value
from students s left join exams e on s.id=e.student_id
group by s.id
order by count(e.id) desc