How to group MySQL rows using a combination of non-null values

Question

How to merge/combine rows by selecting the latest non-null value for each column in the grouped result?

id	user	fruit	number	Creation time
1	simple	apple	null	2022-01-01
2	John	watermelon	32	2022-01-02
3	John	null	72	2022-01-03
4	John	pear	null	2022-01-04

The following methods do not work:

SELECT
  user,
  COALESCE(fruit) as fruit,
  COALESCE(number) as number,
FROM mytable
GROUP BY user
ORDER BY created_at DESC

I hope the result is:

Jane   Apple   Null
John   Pear    72

The problem is that the COALESCE() function does not work with value sets, but only with lists.

Many topics mention using MIN() or MAX() instead of COALESCE() as a solution. But this doesn't solve the problem because I need the new value to overwrite the old value, not the lowest or highest value.

Answer 1

Since COALESCE() is not an aggregate function but can only work with a given list of values, I found @jsowa's workaround in this post: Why "COALESCE" doesn't work with "GROUP BY" used together?

We can use SUBSTRING_INDEX(GROUP_CONCAT()) instead of COALESCE().

Please pay special attention to the ORDER BY clause in GROUP_CONCAT(). The disadvantage is that the delimiter must be an unused character in any value.

SELECT
  user,
  SUBSTRING_INDEX(GROUP_CONCAT(fruit ORDER BY created_at DESC SEPARATOR '|'), '|', 1) AS fruit,
  SUBSTRING_INDEX(GROUP_CONCAT(number ORDER BY created_at DESC SEPARATOR '|'), '|', 1) AS number,
FROM mytable
GROUP BY user;

Return results

Jane   Apple   Null
John   Pear    72