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