[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
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?
More information about the thelist
mailing list