Sorting mysql results by two columns, but with a "hole" column

Question

My initial situation is as follows:

  +------------+-------------+
  | legacyRank | forcedRank  |
  +------------+-------------+
  | 0          | NULL        |
  | 1          | 6           |
  | 2          | NULL        |
  | 3          | 1           |
  | 4          | NULL        |
  | 5          | NULL        |
  | 6          | 2           |
  +------------+-------------+

You can generate this table with the following schema:

CREATE TABLE two_column_order (
  legacyRank VARCHAR(45),
  forcedRank VARCHAR(45)
);

INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (5, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (6, 2); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (7, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (0, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (1, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (2, 6); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (3, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank)
VALUES (4, 1); 


SELECT * FROM two_column_order
order by
  CASE when `forcedRank`  <> NULL THEN `forcedRank`
  ELSE `legacyRank`
  END

The goal is to place every row that does not contain a NULL forcedRank column at the exact location mentioned in this forcedRank column. The expected rendering is as follows:

  +------------+-------------+
  | legacyRank | forcedRank  |
  +------------+-------------+
0 | 0          | NULL        |
1 | 3          | 1           |
2 | 6          | 2           |
3 | 2          | NULL        |
4 | 4          | NULL        |
5 | 5          | NULL        |
6 | 6          | 6           |
  +------------+-------------+

As you can see, if not NULL, each row will take a position sorted by the forcedRank column. When a NULL row is still sorted by the legacyRank column in a position not occupied by a non-NULL row, but the forced row is never moved.

Following this sequence, I tried using the CASE WHEN syntax in ORDER BY like this:

SELECT * FROM two_column_order

order by 
  CASE WHEN (`forcedRank` is NULL )  THEN `legacyRank`
  END ,

  -`forcedRank` DESC,
  `legacyRank`

But the result didn’t really meet my expectations:

+------------+-------------+
| legacyRank | forcedRank  |
+------------+-------------+
| 3          | 1           |
| 6          | 2           |
| 6          | 6           |
| 0          | NULL        |
| 2          | NULL        |
| 4          | NULL        |
| 5          | NULL        |
+------------+-------------+

So how do I make the legacyRank columns order beyond the forcedrank rows without moving them?

Answer 1

NULL cannot be compared like you need to use IS or in your case IS NOT

SELECT * FROM two_column_order
order by
  CASE when `forcedRank`  IS NOT NULL THEN `forcedRank`
  ELSE `legacyRank`
  END

legacyRank	Forced ranking
0	null
1	null
4	1
6	2
3	null
5	null
2	6
7	null

violin

Because the first answer won't give you the correct answer.

I changed the order by adding a decimal point to the original number so that it is larger than the new forced ranking.

It will keep the order, and force the numbers to be smaller than the traditional ranking, it will get the following result

SELECT * FROM two_column_order
order by
  CASE when `forcedRank`  IS NOT NULL THEN `forcedRank` 
  ELSE `legacyRank` + .1
  END

legacyRank	Forced ranking
0	null
4	1
1	null
6	2
3	null
5	null
2	6
7	null

violin