MySQL Merge Data – Using UNION ALL and INNER JOIN

mergeMySQLunion

I try merge a data to one row. I have query:

(select t.`id`, 
        t.`coins_id`, 
        t.`first_coin`, 
        t.`second_coin`, 
        t.`price` as `price_old`, 
        null as `price_young`, 
        t.`time`
from hist_all t
inner join (
  select `first_coin`, 
        MIN(`time`) as `MINDATE` 
  from hist_all group by `first_coin` 
) t1 
on t.`first_coin` = t1.`first_coin` 
and (t.`time` = t1.`MINDATE`) 
WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
and `time` between (NOW() - interval 9999 minute) 
AND NOW()
group by t.`first_coin` 
order by t.`ID`)
UNION ALL
(select t.`id`, 
        t.`coins_id`, 
        t.`first_coin`, 
        t.`second_coin`, 
        null as `col_null_1`, 
        t.`price` as `new`, 
        t.`time`
from hist_all t
inner join (
  select `first_coin`, 
         MAX(`time`) as `MAXDATE` 
   from hist_all 
   group by `first_coin`
) t1 
on t.`first_coin` = t1.`first_coin` 
and (t.`time` = t1.`MAXDATE`) 
WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
and `time` between (NOW() - interval 9999 minute) 
AND NOW()
group by t.`first_coin` 
order by t.`ID`)

This query reurn for example:

enter image description here

I need such a result:

enter image description here

The id, coins_id and time values are irrelevant, I gave them for example.

Best Answer

This can probably be simplified, but in it's simplest form all you need to do is to use an aggregate function for price_old and price_young:

select first_coin, second_coin, max(price_old), max(price_young)
from (
    (select t.`id`, 
            t.`coins_id`, 
            t.`first_coin`, 
            t.`second_coin`, 
            t.`price` as `price_old`, 
            null as `price_young`, 
            t.`time`
    from hist_all t
    inner join (
      select `first_coin`, 
            MIN(`time`) as `MINDATE` 
      from hist_all group by `first_coin` 
    ) t1 
    on t.`first_coin` = t1.`first_coin` 
    and (t.`time` = t1.`MINDATE`) 
    WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
    and `time` between (NOW() - interval 9999 minute) 
    AND NOW()
    group by t.`first_coin` 
    order by t.`ID`)
    UNION ALL
    (select t.`id`, 
            t.`coins_id`, 
            t.`first_coin`, 
            t.`second_coin`, 
            null as `col_null_1`, 
            t.`price` as `new`, 
            t.`time`
    from hist_all t
    inner join (
      select `first_coin`, 
             MAX(`time`) as `MAXDATE` 
       from hist_all 
       group by `first_coin`
    ) t1 
    on t.`first_coin` = t1.`first_coin` 
    and (t.`time` = t1.`MAXDATE`) 
    WHERE (second_coin = 'USD' OR second_coin = 'USDT') 
    and `time` between (NOW() - interval 9999 minute) 
    AND NOW()
    group by t.`first_coin` 
    order by t.`ID`)
) as t
group by first_coin, second_coin
Related Question