Igor Rusinov Igor Rusinov - 7 months ago 7
SQL Question

How to order by two related values in a table

In MySQL database I have a table:

+----+---------+-------------------+------------------+-------------------+


| id | file_id | file_name | is_complementary | complementary_for |


+----+---------+-------------------+------------------+-------------------+


| 55 | 52 | photo.png | 0 | NULL |


| 56 | 53 | photo_edit.png | 1 | 52 |


| 57 | 54 | card.png | 0 | NULL |


| 58 | 55 | card_edit.png | 1 | 54 |


| 59 | 56 | photo_edit_2.png | 1 | 52 |


+----+---------+-------------------+------------------+-------------------+


Table has records of "original" files (
file_id
) and their edited copies ("complementary files"). If file is original
is_complementary
value of such file is
0
, and if not
is_complementary
value is
1
and
complementary_for
value indicates which
file_id
it refers to.

How to sort this table in a way that if original file is displayed, rows right after that are complementary files for that displayed original file? My goal is to get result like this:

+----+---------+-------------------+------------------+-------------------+


| id | file_id | file_name | is_complementary | complementary_for |


+----+---------+-------------------+------------------+-------------------+


| 55 | 52 | photo.png | 0 | NULL |


| 56 | 53 | photo_edit.png | 1 | 52 |


| 59 | 56 | photo_edit_2.png | 1 | 52 |


| 57 | 54 | card.png | 0 | NULL |


| 58 | 55 | card_edit.png | 1 | 54 |


+----+---------+-------------------+------------------+-------------------+

Answer

You can try:

 SELECT *
 FROM <put table name here>
 ORDER BY IF(is_complimentary = 0, file_id, complimentary_for), is_complimentary

The first ordering criterion uses the ID of the original file; an original file and all its complimentary files land next to each other in the sorted result. The second criterion puts the original file before its complimentary files. You can add file_id to get the complimentary files sorted by their creation order (or other criterion, like file_name).

However, be aware that such ordering cannot use an index and is slow on large tables.

Comments