MySQL GROUP BY ORDER по последним в GROUP BY значениям

CREATE TABLE orders (id INT, name VARCHAR (100), rank INT, tm INT);
    INSERT INTO orders VALUES (1,'BTC', 0, 15040),
                                    (2,'ETH', 1, 15040),
                                    (3,'BTN', 2, 15040),
                                    (4,'RPL', 3, 15040),
                                    (5,'BTC', 0, 15102),
                                    (6,'ETH', 2, 15102),
                                    (7,'BTN', 3, 15102),
                                    (8,'RPL', 1, 15102); 

SELECT id, name, rank,
       substr(min(concat(lpad(id,10,'0'),rank)),11)-
       substr(max(concat(lpad(id,10,'0'),rank)),11) diff
  FROM orders GROUP BY name ORDER BY rank

BTC (0)
RPL (1)
ETH (2)
BTN (3)

Наверное будет так:

SELECT o.id, o.name, o.rank, tmp.diff       
FROM orders o
INNER JOIN (SELECT name, max(tm) as tm, 
            SUBSTR(MIN(CONCAT(LPAD(id,10,'0'),rank)),11)- SUBSTR(MAX(CONCAT(LPAD(id,10,'0'),rank)),11) diff 
            FROM orders 
            GROUP BY name
          ) tmp
ON tmp.name = o.name
WHERE o.tm = tmp.tm
ORDER BY o.rank

То есть во внутреннем запросе выбираем имя, максимальное tm и разницу с группировкой, а во внешнем объединяем по имени беря параллельно diff

Вот ещё вариант:

SELECT o.id, o.name, 
   (SELECT r.rank FROM orders r WHERE r.name = o.name ORDER BY id DESC LIMIT 1) as NewRank,
   substr(min(concat(lpad(o.id,10,'0'),o.rank)),11)-
   substr(max(concat(lpad(o.id,10,'0'),o.rank)),11) diff
FROM orders o GROUP BY o.name ORDER BY NewRank ASC

Вариант без извращений с конкатенацией:

SELECT max_id, o1.name, last_rank,
       first_rank-last_rank AS diff
  FROM ( 
         SELECT max(id) AS max_id, min(id) AS min_id, name
            FROM orders
            GROUP BY name
       ) AS o1
       INNER JOIN (
         SELECT id, rank AS last_rank
            FROM orders
       ) AS o2
         ON o2.id=max_id
       INNER JOIN (
         SELECT id, rank AS first_rank
            FROM orders
       ) AS o3
         ON o3.id=min_id
  ORDER BY last_rank;

Второй вариант, без подзапросов в JOIN'ах

SELECT max_id, last.name, last.rank,
       first.rank-last.rank AS diff
  FROM (
         SELECT max(id) AS max_id, min(id) AS min_id
            FROM orders
            GROUP BY name
       ) AS o
       INNER JOIN orders AS last ON last.id=max_id
       INNER JOIN orders AS first ON first.id=min_id
  ORDER BY last.rank;