Reverse join in MySQL

Without the SQL standard RANK() OVER(...), you have to compute the ordering yourself as you discovered.

The RANK() of a row is simply 1 + the COUNT() of all better-ranked rows. (DENSE_RANK(), for comparison, is 1 + the COUNT() of all DISTINCT better ranks.) While RANK() can be computed as a scalar subquery in your SELECT projection — as, e.g., you have done with SELECT (SELECT COUNT(1) ...), ... — I tend to prefer joins:

    SELECT lft.id AS "left_id", lft.name AS "left_name",
           rgt.id AS "right_id", rgt.name AS "right_name"
      FROM (   SELECT s.id, s.name, COUNT(1) AS "rank"     -- Left ranking
                 FROM sequences s
            LEFT JOIN sequences d ON s.id <= d.id
             GROUP BY 1, 2) lft
INNER JOIN (   SELECT s.id, s.name, COUNT(1) AS "rank"     -- Right ranking
                 FROM sequences s
            LEFT JOIN sequences d ON s.id >= d.id
             GROUP BY 1, 2) rgt
           ON lft.rank = rgt.rank
  ORDER BY lft.id ASC;

The only thing i can think to be improved is

SELECT 
  l.id AS left_id, 
  l.name ln, 
  (SELECT COUNT(1) FROM sequences WHERE id<=left_id) AS left_order, 
  r.id AS right_id,
  r.name rn,
  (SELECT COUNT(1) FROM sequences WHERE id>=right_id) AS right_order 
FROM 
  sequences AS l 
  LEFT JOIN 
    sequences AS r ON 1
HAVING
  left_order=right_order;

There are 2 changes that should make this a little bit faster:

1) Calculating right order in reverse order in the first place

2) avoid using SELECT COUNT in the last line.

Edit: I aliased the ln,rn because i couldn't see the columns in fiddle