[thelist] sql order by sizes

[Matt Warden]

On 7/7/07, Brian Cummiskey <brian at hondaswap.com> wrote:
> I have a table with size info like the following:
>
> 2XL
> XL
> Med
> Sm
> Lg
> 4XL
> 2XL
>
> etc
>
>
> I'm trying to devise a way to display these in order by size, so that it
> goes smallest through largest (sm - 4XL)

You don't need a sizeOrder table, but there probably should be a size table:

SIZE
------------
id
sizeDesc
sizeNumber

But, forget that for the moment. You can fake it like so (depending on
your dbms):

select *
from (select *, (case size when 's' then 0 when 'm' then 1 ...) as sizeNumber
          from yourtable)
where ...
order by sizeNumber asc

You did'nt say where this size data is. If it's in its own table
already, then you just need to add that other column. If it's
denormalized into the products table, then you either need to normlize
it as I suggested above or do the translation in an innerview in the
example sql above. There may be other ways to do it, as well, so if
both of these don't work for you, we can put our thinking caps on.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.