CODEWITHSUNDEEP
mysqlsqlpivot-table
crmepham
crmepham

Reputation: 4760

Mysql pivot table cannot order by date

I would like to get the most recent price (gbp,usd,eur) of a particular cryptucurrency from the following table:

mysql> select * from blockchain_currency_price;
+----+---------------------+----------+------+-------+---------------------+--------------+
| id | blockchain_currency | currency | type | value | created             | created_user |
+----+---------------------+----------+------+-------+---------------------+--------------+
|  1 | ETH                 | USD      | buy  |  1076 | 2018-01-27 10:55:09 | system       |
|  2 | ETH                 | USD      | sell |  1054 | 2018-01-27 10:55:09 | system       |
|  3 | BTC                 | USD      | buy  | 11264 | 2018-01-27 10:55:09 | system       |
|  4 | BTC                 | USD      | sell | 11041 | 2018-01-27 10:55:10 | system       |
|  5 | ETH                 | GBP      | buy  |   760 | 2018-01-27 10:55:10 | system       |
|  6 | ETH                 | GBP      | sell |   745 | 2018-01-27 10:55:11 | system       |
|  7 | BTC                 | GBP      | buy  |  7954 | 2018-01-27 10:55:11 | system       |
|  8 | BTC                 | GBP      | sell |  7797 | 2018-01-27 10:55:12 | system       |
|  9 | ETH                 | EUR      | buy  |   865 | 2018-01-27 10:55:12 | system       |
| 10 | ETH                 | EUR      | sell |   848 | 2018-01-27 10:55:12 | system       |
| 11 | BTC                 | EUR      | buy  |  9062 | 2018-01-27 10:55:13 | system       |
| 12 | BTC                 | EUR      | sell |  8883 | 2018-01-27 10:55:13 | system       |
| 13 | ETH                 | USD      | buy  |  1068 | 2018-01-27 12:18:42 | system       |
| 14 | ETH                 | USD      | sell |  1046 | 2018-01-27 12:18:43 | system       |
| 15 | BTC                 | USD      | buy  | 11077 | 2018-01-27 12:18:43 | system       |
| 16 | BTC                 | USD      | sell | 10857 | 2018-01-27 12:18:44 | system       |
| 17 | ETH                 | GBP      | buy  |   754 | 2018-01-27 12:18:44 | system       |
| 18 | ETH                 | GBP      | sell |   739 | 2018-01-27 12:18:44 | system       |
| 19 | BTC                 | GBP      | buy  |  7822 | 2018-01-27 12:18:45 | system       |
| 20 | BTC                 | GBP      | sell |  7667 | 2018-01-27 12:18:45 | system       |
| 21 | ETH                 | EUR      | buy  |   859 | 2018-01-27 12:18:46 | system       |
| 22 | ETH                 | EUR      | sell |   842 | 2018-01-27 12:18:46 | system       |
| 23 | BTC                 | EUR      | buy  |  8912 | 2018-01-27 12:18:46 | system       |
| 24 | BTC                 | EUR      | sell |  8735 | 2018-01-27 12:18:47 | system       |
+----+---------------------+----------+------+-------+---------------------+--------------+
24 rows in set (0.00 sec)

I have attempted to do this using the following query:

select 
  blockchain_currency BCCurrency,
  max(if(currency='GBP', value, 0)) as GBP,
  max(if(currency='USD', value, 0)) as USD,
  max(if(currency='EUR', value, 0)) as EUR
from blockchain_currency_price
group by blockchain_currency
order by created desc;

Which gives me the following error:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'server.blockchain_currency_price.created' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

If I remove the order by clause I get the following:

mysql> select 
    ->   blockchain_currency BCCurrency,
    ->   max(if(currency='GBP', value, 0)) as GBP,
    ->   max(if(currency='USD', value, 0)) as USD,
    ->   max(if(currency='EUR', value, 0)) as EUR
    -> from blockchain_currency_price
    -> group by blockchain_currency;
+------------+------+-------+------+
| BCCurrency | GBP  | USD   | EUR  |
+------------+------+-------+------+
| BTC        | 7954 | 11264 | 9062 |
| ETH        |  760 |  1076 |  865 |
+------------+------+-------+------+
2 rows in set (0.01 sec)

But these are not the most recent entries.

How do I create the above pivot whilst grouping and then order by the created date to get only the most recent entries?

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You can just use a where clause:

    select bcp.*
    from blockchain_currency_price bcp
    where bcp.created = (select max(bcp2.created)
                         from blockchain_currency_price bcp2
                         where bcp2.blockchain_currency = bcp.blockchain_currency and
                               bcp2.currency = bcp.currency
                        );

This produces one row for each currency pair. If you really need this to be pivoted for one row per blockchain_currency, you can pivot with this where clause.

select bcp.blockchain_currency,
         max(case when currency = 'GBP' then value end) as GBP,
         max(case when currency = 'USD' then value end) as USD,
         max(case when currency = 'EUR' then value end) as EUR
from blockchain_currency_price bcp
where bcp.created = (select max(bcp2.created)
                     from blockchain_currency_price bcp2
                     where bcp2.blockchain_currency = bcp.blockchain_currency and
                           bcp2.currency = bcp.currency       
                    )
group by blockchain_currency;

Upvotes: 1

Related Questions