How can I sequence this MySQL table correctly

Question

I've got a table containing a mixture of left and right shoes, some of which are waterproof.

I need to write a query to sort them alphabetically, but- when the name is the same- use the waterproof column ahead of left/right.

e.g.

+-------------------------+------------+------------+
| Shoe name               | Waterproof | Left/Right |
+-------------------------+------------+------------+
| boot                    |     0      |   left     |
| sandal                  |     0      |   left     |
| shoe                    |     1      |   left     |
| boot                    |     1      |   left     |
| boot                    |     0      |   right    |
| boot                    |     1      |   right    |
| sandal                  |     0      |   right    |
| shoe                    |     1      |   right    |
+-------------------------+------------+------------+

Should be sorted as such...

+-------------------------+------------+------------+
| Shoe name               | Waterproof | Left/Right |
+-------------------------+------------+------------+
| boot                    |     0      |   left     |
| boot                    |     0      |   right    |
| boot                    |     1      |   left     |
| boot                    |     1      |   right    |
| sandal                  |     0      |   left     |
| sandal                  |     0      |   right    |
| shoe                    |     1      |   left     |
| shoe                    |     1      |   right    |
+-------------------------+------------+------------+

Can it be done?

Answer 1

But what if the sandals were not waterproof?

On your modified data structure, the following should work:

SELECT `Shoe name`, `Waterproof`, `Left/Right`
 FROM shoe_table
  ORDER BY CONCAT( `Shoe name`, ', ', `Waterproof` ), `Left/Right`;

---

You can try:

select * from shoes order by 2, 1;

OR by column name:

select * from shoes order by Waterproof, `Shoe name`;

Answer 2

Try this:

ORDER BY ShoeName,Waterproof 

Answer 3

This requires parsing the shoes column. The easiest way is using substring_index():

order by substring_index(ShoeName, '(', 1), Waterproof, ShowName