SELECT q.*from
  (SELECT h.*, c.symbol
   FROM `coin_history` AS h
   JOIN coins AS c ON h.coin_id = c.id
   ORDER BY h.date DESC
   LIMIT 50) AS q
GROUP BY q.coin_id
ORDER BY q.rank ASC

在派生表中，从特定硬币的历史中获取日期的最大值。
将这个结果集与主表连接起来，只获取与硬币最近日期对应的行。
最终，使用 ORDER BY 按等级升序得到结果。
尝试：

SELECT 
  c.coin_id, 
  c.symbol, 
  ch.rank 
FROM 
  coins AS c 
JOIN 
  coin_history AS ch 
    ON ch.coin_id = c.coin_id 
JOIN 
  (
   SELECT coin_id, 
          MAX(date) AS max_date 
   FROM coin_history 
   GROUP BY coin_id 
  ) AS dt 
    ON ch.coin_id = dt.coin_id AND 
       ch.date = dt.max_date
ORDER BY ch.rank

mysql还有一个更简单的方法，但它只是mysql，所以在代码中需要数据库可移植性时不要使用这个方法。
使用group\u concat和嵌套的substring\u index函数来获取第一个元素/项也可以用于获取保留正确分组信息的额外列。。
注： SET SESSION group_concat_max_len = @@max_allowed_packet; 使用非常重要，否则组\u concat将仅显示1024字节。。
查询

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
   coins.id AS coin_id
 , coins.symbol
 , CAST( 
      SUBSTRING_INDEX(
        SUBSTRING_INDEX(
           GROUP_CONCAT(coin_history.rank ORDER BY coin_history.date DESC)
         , ','
         , 1
        )
        , ','
       , -1
   ) AS UNSIGNED) AS rank
FROM 
 coins
INNER JOIN
 coin_history
ON
 coins.id = coin_history.coin_id

GROUP BY 
   coins.id
 , coins.symbol

ORDER BY 
 rank

结果

| coin_id | symbol | rank |
| ------- | ------ | ---- |
| 1       | BTC    | 1    |
| 2       | ETH    | 2    |
| 4       | BCH    | 3    |
| 3       | XRP    | 4    |

db小提琴演示