[thelist] sql order by sizes

Brian Cummiskey brian at hondaswap.com
Sat Jul 7 17:57:35 CDT 2007

Thanks to all 3 of you for the ideas.  I probably should have been more 

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:

    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?

More information about the thelist mailing list