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?

you are storing an additional 7-8 characters verses a bit (1 or 0) representing the foot. — Fallenreaper, Jan 07 '14 at 17:24
on another note, if it is a column, you could do something: select * from footwear order by showname asc, shoe asc, waterproof asc; which would give you your output — Fallenreaper, Jan 07 '14 at 17:26
shoe.left = 0, shoe.right = 1... then asc will put in that order. :) — Fallenreaper, Jan 07 '14 at 17:27
ORDER BY shoe_name,waterproof,lef_right DESC - or something like that. Also, take a look at FIELD() — Strawberry, Jan 07 '14 at 17:51

Ravinder Reddy · Accepted Answer · 2014-01-07T17:55:17.927

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`;

edited Jan 07 '14 at 17:55

answered Jan 07 '14 at 17:08

Ravinder Reddy

23,692
6
52
82

Hmm..Yes ok. It works for this example. But what if the sandals were not waterproof? In fact, I'll change it so they're not. It makes the point clearer. – Urbycoz Jan 07 '14 at 17:17

BWS · Answer 2 · 2014-01-07T17:19:36.360

0

Try this:

ORDER BY ShoeName,Waterproof

edited Jan 07 '14 at 17:19

answered Jan 07 '14 at 17:08

BWS

3,786
18
25

That would not work. It would have non-waterproof left boot sequenced before waterproof left boot. – Urbycoz Jan 07 '14 at 17:17

score 0 · Answer 3 · answered Jan 07 '14 at 17:22

0

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

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

answered Jan 07 '14 at 17:22

Gordon Linoff

1,242,037
58
646
786

How can I sequence this MySQL table correctly

3 Answers3