How to join rows in order without duplication

Question

I have an history table that stores whenever a modem was activated or disactivated. I want to join both rows: activation and deaactivation, in one.

I tryed with left join. But I get every actation row joined with every deaactivation row.

Activation
id_history  id_modem  date        Type
============================================
1           2         2017-07-14  Activation
3           2         2018-01-18  Activation


Deactivation
id_history  id_modem   date       Type
==============================================
2           2          2017-09-07 Deactivation
4           2          2018-02-19 Deactivation

I want to get this:

id_history_act  id_modem  date_act    type_act  id_history_dea  date_dea    type_dea
======================================================================================
1               2         2017-07-14  Act       2               2017-09-07  Dea
3               2         2018-01-18  Act       4               2018-02-19  Dea

But I am getting this when I make:

select 
    ha.id_history id_history_act,
    ha.id_modem,
    ha.date date_act,
    ha.type type_act,

    hd.id_history id_history_dea,
    hd.date date_dea,
    hd.type type_dea
from 
    history_activation ha 
    left join history_deactivation hd on(hd.id_modem = ha.id_modem);


id_history_act  id_modem  date_act    type_act  id_history_dea  date_dea    tupe_dea
==================================================================================
1               2         2017-07-14  Act       2               2017-09-07  Dea
1               2         2017-07-14  Act       4               2018-02-19  Dea
3               2         2018-01-18  Act       2               2017-09-07  Dea
3               2         2018-01-18  Act       4               2018-02-19  Dea

I tryed using a rank but I need to use variables and variables cannot be used in views.

And using count(*) with another join inside activation and deactivation queries is too slow.

Edit: This is the original table

HistoryTable
id_history  id_modem   date         Type 
====================================================
1           2          2017-07-14   Activation
2           2          2017-09-07   Deactivation
3           2          2018-01-18   Activation
4           2          2018-02-19   Deactivation

Answer 1

If the ids and dates are consistent like your sample data, then you need a self join and then select the minimum id_history and minimum date for type = Deactivation for each type = Activation:

select
  h1.id_history id_history_act, 
  h1.id_modem, 
  h1.date date_act,
  min(h2.id_history) id_history_des,
  min(h2.date) date_des
from HistoryTable h1 left join HistoryTable h2
on h2.id_modem = h1.id_modem and h2.date > h1.date
where h1.type = 'Activation' and h2.type = 'Deactivation'
group by h1.id_history, h1.id_modem, h1.date

See the demo.
Results:

| id_history_act | id_modem | date_act            | id_history_des | date_des            |
| -------------- | -------- | ------------------- | -------------- | ------------------- |
| 1              | 2        | 2017-07-14 00:00:00 | 2              | 2017-09-07 00:00:00 |
| 3              | 2        | 2018-01-18 12:03:14 | 4              | 2018-02-19 12:15:07 |

Answer 2

You just need to use the ORDER BY clause in your query

try this:

select 
ha.id_history id_history_act,
ha.id_modem,
ha.date date_act,

hd.id_history id_history_des,
hd.date date_des,
from 
history_activation ha 
left join history_desctivation hd on(hd.id_modem = ha.id_modem);
order by date_act