CODEWITHSUNDEEP
mysqldatabasesorting
Igor R.
Igor R.

Reputation: 429

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 | 
+----+---------+-------------------+------------------+-------------------+

Upvotes: 2

Views: 47

Answers (1)

axiac
axiac

Reputation: 72256

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.

Upvotes: 1

Related Questions