[thelist] sql order by sizes
Tab Alleman
talleman at Lumpsum.com
Mon Jul 9 08:55:34 CDT 2007
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 !
>
More information about the thelist
mailing list