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 of each column in the grouped result? id user fruit number creation time 1 Jane apple empty 2022-01-012 John watermelon 322022-01-023 John empty 722022-01-034 John pear empty 2022-01-04 The following method does not work: SELECTuser,COALESCE(fruit)asfruit ,COALESCE(number)asnumber,FROMmytableGROUPBYuserORDERBYc

P粉979586159 · Answer

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

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