'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 |
|---|
