Ordering AA before A

I'm doing an e-commerce website for a client who sells lingerie, I've wri开发者_高级运维tten up a bra size picker for them but they've come back to me today with a slight issue.

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

Is there a simple way I can get MySQL to order AA first, then A, B etc.?

I would add another column "orderid" (INTEGER or even TINYINT) to your table, and order by that column instead.

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

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

But a better solution would be to create a conversion table which would store all possible sizes (European, Japanese etc) including metrical on which yoг can order.

You may use it to build conversion charts and show the sizes in person's preferred system as well.

Would putting and underscore before "A" so "_A" work and be acceptable, you could strip it for display purposes? (Not sure if this will work or not)

Alternatively have a sort code seperate from the bra size field for sorting.

Create a lookup table for the ordering

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

You can order by a value derived from a column. You can use a CASE operator to specify the order: ...

source

this would be your select:

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

sorts AA first, then A then B :)

I would prefer to introduce a column sequenceNo to store the order on the server. Imagine other pickers

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