[thelist] sql order by sizes

[Tab Alleman]

The simple answer to your original question:

ORDER BY CASE sizecode
	WHEN xs THEN 0
	WHEN sm THEN 1
	WHEN md THEN 2
(etc...)
END ASC



> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Brian Cummiskey
> Sent: Saturday, July 07, 2007 6:58 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] sql order by sizes
> 
> 
> Thanks to all 3 of you for the ideas.  I probably should have 
> been more 
> specific.
> 
> DBMS is MS sql2000
> 
> The table is a mess of styles with colors, sizes, inseam, 
> chest, neck, 
> head, .... you name it.  It's a horrid mess, but i can't 
> change it due 
> to syncing with other systems.
> 
> I've created a normalized temp table of the info I need-  the 
> size and 
> color pairs (as not all sizes are available in all colors)
> 
> sample data for a product:
> 
> 
> ID  sizecode  colorcode  sizetext                     colortext
> ---------------------------------------------------------------------
> 1    2XL         AP          2X-LARGE                ARGYLE 
> PURPLE      
> 150    LG          AP          LARGE                   ARGYLE 
> PURPLE      
> 190    MD          AP          MEDIUM                  ARGYLE 
> PURPLE      
> 230    SM          AP          SMALL                   ARGYLE 
> PURPLE      
> 270    XL          AP          X-LARGE                 ARGYLE 
> PURPLE      
> 310    XS          AP          X-SMALL                 ARGYLE 
> PURPLE      
> 2    2XL         BB          2X-LARGE                BERMUDA 
> BLUE       
> 41    3XL         BB          3X-LARGE                BERMUDA 
> BLUE       
> 78    4XL         BB          4X-LARGE                BERMUDA 
> BLUE       
> 114    5XL         BB          5X-LARGE                
> BERMUDA BLUE       
> 151    LG          BB          LARGE                   
> BERMUDA BLUE       
> 191    MD          BB          MEDIUM                  
> BERMUDA BLUE       
> 231    SM          BB          SMALL                   
> BERMUDA BLUE       
> 271    XL          BB          X-LARGE                 
> BERMUDA BLUE       
> 311    XS          BB          X-SMALL                 
> BERMUDA BLUE       
> 
> (+ 300 more rows trimmed)
> 
> 
> To get this table, i'm using the following SP:
> 
> DECLARE @temp TABLE
> (
>     id INT identity,
>         l1code VARCHAR(10),
>     l2code varchar(10),
>     l1name varchar(50),
>     l2name varchar(50)
>    
> )
> 
> 
> insert into @temp
> select distinct s.l1code, s.l2code, s.l1desc, s.l2desc
> from styleitemdata s
> inner join inventory i with (nolock) on i.edpno = s.EDPNO
> where i.sku like 'PRO8104%' And i.active='Y'
> 
> 
> 
> 
> select * from @temp
> order by l2name, l1name
> 
> 
> I'm trying to make dynamic select boxes (color first) so that 
> the size 
> box is rendered with the available size options of the selected color 
> only, and to display the sizes in order.
> 
> 
> Should i not bother with the sub query as Matt suggested, and instead 
> use the power of the JS array for my sorting?
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>