[thelist] sql order by sizes

Matt Warden mwarden at gmail.com
Sat Jul 7 17:26:03 CDT 2007

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:


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

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list