CODEWITHSUNDEEP
mysqlsql
Slavo S.
Slavo S.

Reputation: 11

mysql query combine 2 selects on one table

How can I combine 2 selects on one table like this?

Table T1:

id | status | ts
1  | 0      |2019-02-22 00:00:00
2  | 0      |2019-02-22 00:00:00
3  | 0      |2019-02-22 00:00:00
1  | 1      |2019-02-22 01:00:00
3  | 1      |2019-02-22 01:30:00
1  | 0      |2019-02-22 02:00:00
3  | 0      |2019-02-22 03:00:00

I want to find last values of status = 0 and status = 1 for each ID

1  | 0      |2019-02-22 02:00:00 with 1       | 1      |2019-02-22 01:00:00
2  | 0      |2019-02-22 00:00:00
3  | 0      |2019-02-22 03:00:00 with 3       | 1      |2019-02-22 01:30:00

I tried:

SELECT id, status, MAX(ts) 
 from T1 
where status = 1 Union SELECT id, status, MAX(ts) from T1 where status = 0

but not ok... :o(

And maybe calculate time difference between 2 states of change for each ID...

Upvotes: 0

Views: 56

Answers (1)

GMB
GMB

Reputation: 222592

Conditional aggregation should to the trick :

SELECT 
    id, 
    MAX(CASE WHEN status = 0 THEN ts END) ts_0, 
    MAX(CASE WHEN status = 1 THEN ts END) ts_1 
FROM T1
GROUP BY id

Demo on DB Fiddle :

| id  | ts_0                | ts_1                |
| --- | ------------------- | ------------------- |
| 1   | 2019-02-22 02:00:00 | 2019-02-22 01:00:00 |
| 2   | 2019-02-22 00:00:00 |                     |
| 3   | 2019-02-22 03:00:00 | 2019-02-22 01:30:00 |

Upvotes: 1

Related Questions