Posted by IchBin on 10/09/37 11:56

Andy Hassall wrote:
> On Fri, 25 Aug 2006 16:55:39 GMT, "Frankie" <frankie66@earthlink.net> wrote:
>
> [snip some insert statements]
>> But this results in random entries by size.
>>
>> Is there a way to order these entries? If not, how would I use usort() to
>> get the order I want?
>
> Data in a table is unordered - you must always use an "ORDER BY" clause in the
> SELECT statement if you want it out in a particular order. There's nothing you
> can do on INSERT to guarantee an ordering - it doesn't work like that.
>
> e.g.
>
> select itemNumber, size, inStock
> from inStockApparel
> order by itemNumber, size
>

If I understand this correctly doing a Sort on the select will not give
you the order you want: "order: S, M, L, XL". Sorting you will get either

- Ascending order of: "L, M, S, XL"
- Descending order of: "XL, S, M, L"

You could a create of a table for sizes and load it (Quick Test below,
sorry it is HSQLDB)

CREATE CACHED TABLE size_table (
ID INTEGER PRIMARY KEY,
size_abbr VARCHAR( 255 ) DEFAULT ''
);
insert into size_table (id, size_abbr) VALUES (0,'S');
insert into size_table (id, size_abbr) VALUES (1,'M');
insert into size_table (id, size_abbr) VALUES (2,'L');
insert into size_table (id, size_abbr) VALUES (3,'XL');

CREATE CACHED TABLE inStockApparel (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY( START WITH 1, INCREMENT BY 1 ),
itemNumber INT DEFAULT 0,
size INT DEFAULT 0,
instock CHAR DEFAULT 'N',
PRIMARY KEY( ID )
);
insert into inStockApparel(itemNumber,size,instock) VALUES (1000,0,'Y');
insert into inStockApparel(itemNumber,size,instock) VALUES (1000,1,'Y');
insert into inStockApparel(itemNumber,size,instock) VALUES (1000,2,'N');
insert into inStockApparel(itemNumber,size,instock) VALUES (1000,3,'Y');

Given the above definitions your select would look something like this:

SELECT i.itemNumber, s.size_abbr, i.inStock
FROM inStockApparel i, size_table s
where s.id = i.size
ORDER by i.itemNumber, s.id;

Gives you:

ITEMNUMBER SIZE_ABBR INSTOCK
---------- --------- -------
1000 S Y
1000 M Y
1000 L N
1000 XL Y

--
Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

[Back to original message]