Sort by the largest ID and group the similar ones

Here are two alternatives:

The first is to use a join (with the table itself). This is a portable solution, since it only uses standard SQL language elements or functionalities, and is available in (almost) all database management systems.

SELECT P1.*
  FROM produtos P1
  LEFT JOIN 
  ( 
     SELECT RELATED, MAX(ID) MAX_ID 
       FROM produtos 
      WHERE RELATED IS NOT NULL 
      GROUP BY RELATED
  ) AS P2
    ON P2.related = P1.RELATED
 ORDER BY CASE WHEN P2.RELATED IS NULL THEN P1.ID ELSE P2.MAX_ID END DESC, P1.ID DESC

The idea is to identify the products for which there is information in the RELATED column, and for each of the RELATED codes identify the corresponding maximum ID. This information will be used as sorting criterion as follows:

ORDER BY CASE WHEN P2.RELATED IS NULL THEN P1.ID ELSE P2.MAX_ID END DESC

The result will be the one you indicated in your question:

| ID  | TITLE    | RELATED |
| --- | -------- | ------- |
| 5   | casaco   |         | 
| 4   | moletom  | 123456  |
| 2   | camiseta | 123456  |
| 3   | calca    |         |
| 1   | tenis    |         |

The second alternative makes use of the window functions available in the latest versions of MySQL.

SELECT ID,
       TITLE,
       RELATED,
       MAX(ID) OVER (PARTITION BY CASE WHEN RELATED IS NOT NULL THEN RELATED ELSE ID END) AS SortingOrder
  FROM produtos
ORDER BY 4 DESC, ID DESC
;

The result will be the same as the previous version.

| ID  | TITLE    | RELATED | SortingOrder |
| --- | -------- | ------- | ------------ |
| 5   | casaco   |         | 5            |
| 4   | moletom  | 123456  | 4            |
| 2   | camiseta | 123456  | 4            |
| 3   | calca    |         | 3            |
| 1   | tenis    |         | 1            |

SELECT id, 
       title, 
       related 
  FROM produtos
 ORDER BY 
  CASE WHEN related IS NULL 
        THEN 1
        ELSE 0
   END, related;

Result:

  

id title related

     

2 t-shirt 123456

     

4 sweatshirt 123456

     

1 tennis NULL

     

3 calca NULL

     

5 NULL jacket

(5 row (s) affected)

You can not rate it that way, as the rating goes on a hierarchy SQL sorts the COD and within the cod sorts related but if you the main objective is the grouping of related and in descending order maybe it will help.

or this helps

SELECT title,
       related,
       cod 
  FROM produtos
 ORDER BY 
  CASE WHEN related IS NULL 
        THEN 1
        ELSE 0
   END, related, cod DESC;

  

title related cod

     

sweatshirt 123456 4

     

T-Shirt 123456 2

     

NULL coat 5

     

calca NULL 3

     

tennis NULL 1

(5 row (s) affected)