I want to merge two rows.
Currently my table looks something like this –
| equipment_number | etd | eta | atd | ata |
|---|---|---|---|---|
| MAHE8346461 | 2023-02-03 10:02:00 | 2023-01-03 10:02:00 | null | null |
| MAHE8346461 | null | null | 2022-02-03 10:02:00 | null |
| MAHE8346462 | null | 2022-02-04 13:02:00 | null | 2022-02-04 13:02:00 |
| MAHE8346462 | 2022-02-04 13:02:00 | 2022-02-04 13:02:00 | 2022-02-03 10:02:00 | null |
| MAHE8346462 | null | 2022-02-04 13:02:00 | null | null |
the resultant output expected is –
| equipment_number | etd | eta | atd | ata |
|---|---|---|---|---|
| MAHE8346461 | 2023-02-03 10:02:00 | 2023-01-03 10:02:00 | 2022-02-03 10:02:00 | null |
| MAHE8346462 | 2022-02-04 13:02:00 | 2023-01-03 10:02:00 | 2022-02-03 10:02:00 | 2022-02-04 13:02:00 |
so I need to combine/merge the multiple rows with same equipment_number into one to reduce the row numbers.
>Solution :
You may do so by using group by and the aggregation function max() or min() depending on whether you want to keep the oldest or the newest records.
select equipment_number, max(etd) as etd, max(eta) as eta, max(atd) as atd, max(ata) as ata
from mytable
group by equipment_number
order by equipment_number