'sql get min of subgroup within group for each subgroup on 1 row

I am trying to find the first message of a subgroup, for all subgroups. The result should be on one single row.

Here is an example schema.

CREATE TABLE test
    (`name` varchar(10), `message_id` int(10), `timing` datetime)
;

INSERT INTO test
VALUES
     ('John', 1, '2018-07-02 2:09:01'),
     ('Peter', 1, '2018-07-02 2:08:54'),
     ('John', 1, '2018-07-02 2:09:04'),
     ('Peter', 2, '2018-07-02 2:09:09')
;

http://www.sqlfiddle.com/#!9/56e480 I can manage to get the first message of each subgroup in two seperate queries, but not in a single one:

select distinct message_id, min(timing) as first_message_peter from test
where name = 'Peter'
group by 1

Resulting in:

message_id| first_message_peter 
1         | 2018-07-02 02:08:54 
2         | 2018-07-02 02:09:09 

What I would like is the following:

message_id| first_message_peter | first_message_john
1         | 2018-07-02 02:08:54 | 2018-07-02 02:09:01
2         | 2018-07-02 02:09:09 | NULL

Could someone help me in the right direction?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source