How to group MySQL rows by combining values that are not null?

Question

How can I merge/combine rows by selecting the most recent not-null value of each column in a grouped result?

id	user	fruit	number	created_at
1	Jane	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 does not work:

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

I want the result to be:

Jane   Apple   Null
John   Pear    72

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

Many topics refer to MIN() or MAX() instead of COALESCE() as a solution. It will not solve this problem as I need the newer value to override the older. Not the lowest or highest value.

Answer 1

As COALESCE() is not an aggregating function but works with a given list of values only, I found a workaround by @jsowa in this thread: Why doesn't "COALESCE" work with "GROUP BY"?

Instead of COALESCE() we can use SUBSTRING_INDEX(GROUP_CONCAT()).

Pay extra attention to the ORDER BY clause inside the GROUP_CONCAT(). The downside is that the separator must be a character not used in any of the values.

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;

Returns

Jane   Apple   Null
John   Pear    72