Order AA before A

Question

I make an e-commerce site for a client who sells underwear, I wrote a bra size picker for them, but they came back to me today with a little problem.

With the size of the bra, AA is smaller than A, so it should appear before then on the chart, but when I use the MySQL order in size, obviously it puts A, then AA, then B, etc.

Is there an easy way to get MySQL to order AA first, then A, B, etc.?

Answer 1

Assuming A is the only possible duplicate letter, you can do this:

 SELECT * FROM bra ORDER BY LENGTH(size) DESC, size 

But the best solution would be to create a conversion table that retains all possible sizes (European, Japanese, etc.), including a metric on which you can order.

You can use it to build transformation charts and display sizes in your own preferred system.

Answer 2

I would add another "orderid" column to the table (INTEGER or even TINYINT) and sort by that column.

Answer 3

By putting and emphasizing before "A" so "_A" works and is acceptable, can you strip it for display? (Not sure if this will work or not)

As an alternative to sorting, you can set the sorting code separately from the bra size field.

Answer 4

You can order the value obtained from the column. You can use the CASE statement to specify the order: ...

a source

It will be your choice:

 select id, val from test order by case val when 'AA' then 1 when 'A' then 2 when 'B' then 3 END 

sorts first AA, then A, then B :)

Answer 5

I would prefer to enter the sequenceNo column to save the order on the server. Imagine other collectors.

• XS
• S
• M
• L
• XL
• ...

Answer 6

Create a lookup table to organize

 Table bra_order id integer auto_increment not null bra_size char(3) sort_id integer not null 

Then do something like:

 SELECT i.* FROM inventory LEFT JOIN bra_order b ON (b.bra_size = i.bra_size) ORDER BY b.sort_id