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.

tim · Accepted Answer · 2022-03-22 16:31:43Z

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

answered Mar 22, 2022 at 16:31

tim

2,7363 gold badges33 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SOS Over a year ago

(Moved) Very nice, and agreed much sleeker! I learned something new, so thanks for that :)

tim Over a year ago

@SOS Thank you. Happy to hear that. :)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related