Greatest-n-per-group in a one-to-many relationship 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 &lt;= 3;
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 &lt;= 3;