0

I'm currently stuck with this (probably) simple query, in which I want to output the newest N TickTimes for each CurrencyID(The newest records added to the table). For only the table "Ticker" I can make this, but in the output I also want to display the MarketName, which is located in the "Currency" table.

Currency
+------------+--------------+------------------+------------+----------+--------------+------------+
| CurrencyID | CurrencyName | CurrencyNameLong | MarketName | IsActive | MinTradeSize | TxFee      |
+------------+--------------+------------------+------------+----------+--------------+------------+

Ticker
+----------+------------+---------------------+------------+------------+------------+---------------+
| TickerID | CurrencyID | TickTime            | Bid        | Ask        | Last       | Volume        |
+----------+------------+---------------------+------------+------------+------------+---------------+

A possible output will look something like this. (Except I want to return maybe 100 of the last TickTime for each CurrencyID)

+------------+------------+---------------------+------------+---------------+
| CurrencyID | MarketName | TickTime            | Last       | Volume        |
+------------+------------+---------------------+------------+---------------+
|          1 | BTC-LTC    | 2017-08-29 16:40:00 | 0.01169000 | 2499.55344592 |
|          1 | BTC-LTC    | 2017-08-29 17:40:04 | 0.01169000 | 2502.10724529 |
|          2 | BTC-VTC    | 2017-08-29 16:40:01 | 0.00021742 | 2791.78613576 |
|          2 | BTC-VTC    | 2017-08-29 17:40:05 | 0.00021742 | 2791.78612635 |
|          3 | BTC-DASH   | 2017-08-29 16:40:02 | 0.07104060 | 660.88002045  |
|          3 | BTC-DASH   | 2017-08-29 17:40:05 | 0.07104090 | 660.87899945  |
|          4 | BTC-XMR    | 2017-08-29 16:40:02 | 0.02178855 | 3681.62291540 |
|          4 | BTC-XMR    | 2017-08-29 17:40:06 | 0.02189143 | 3682.28908088 |
|          5 | BTC-CANN   | 2017-08-29 16:40:03 | 0.00001154 | 1449.06053986 |
|          5 | BTC-CANN   | 2017-08-29 17:40:06 | 0.00001145 | 1447.45006154 |
...
...
|         20 | BTC-BCC    | 2017-08-29 16:40:10 | 0.14265409 | 5196.39621496 |
|         20 | BTC-BCC    | 2017-08-29 17:40:14 | 0.14220000 | 5196.84118604 |
+------------+------------+---------------------+------------+---------------+

EDIT I managed to come up with a query that suits my need. Probably not the most efficient one, but the table is not too big...

select c.CurrencyID, c.MarketName, t.TickTime, t.Last, t.Volume
from (select t.*,
         (@rn := if(@v = CurrencyID, @rn + 1,
                    if(@v := CurrencyID, 1, 1)
                   )
         ) as rn
  from Ticker t cross join
       (select @v := -1, @rn := 0) params
  order by CurrencyID, TickTime desc
 ) as t
INNER JOIN Currency c 
    on c.CurrencyID=t.CurrencyID
where
    rn <= 3;
  • adding the market name is the easy part. You said you already have the query for just ticker already? What is your existing query so far? In general it will be something like: select ticks.CurrencyId, MarketName, ticks.etc from (existing ticker select)ticks inner join Currency curr on ticks.CurrencyId=curr.CurrencyId – indiri Aug 29 '17 at 17:51
  • I updated the question with the answer. I came up with a query that gives me the correct output – patski Aug 29 '17 at 18:13
  • You should post that as an answer so that it can help others. It doesn't do much good when posted as part of the question. – Joe W Aug 29 '17 at 18:18
  • Oh, I didn't think about that. Done it now. Thanks :) – patski Aug 29 '17 at 18:20
0

I managed to come up with a query that suits my need. Probably not the most efficient one, but the table is not too big...

select c.CurrencyID, c.MarketName, t.TickTime, t.Last, t.Volume
from (select t.*,
         (@rn := if(@v = CurrencyID, @rn + 1,
                    if(@v := CurrencyID, 1, 1)
                   )
         ) as rn
  from Ticker t cross join
       (select @v := -1, @rn := 0) params
  order by CurrencyID, TickTime desc
 ) as t
INNER JOIN Currency c 
    on c.CurrencyID=t.CurrencyID
where
    rn <= 3;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.