Posted by Branco Medeiros on 11/17/05 19:53

Laphan wrote:
> ARRRRRRRGGGGGHHHHH!!
<snip>
> INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
> ('A001',''),
> ('A002','A001'),
> ('A003','A001'),
> ('A004','A001'),
> ('A005','A001'),
> ('PCHW01',''),
> ('MHW01',''),
> ('FD01',''),
> ('ELEC01',''),
> ('MHW02','MHW01');
<snip>
> As you can see from the table structure, this table consists of 2 field
> values. The 1st is the category code and the 2nd is the level is at. If a
> catid has a level of nothing, eg '', then it means that it is a root level
> category. If a catid has a another cat's catid in it's level, eg B01 has
> P01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.
>
> All I want to do is query this table and bring back the data so that
> alphabetically it goes root level cat A1, then all the sub-cats for this
> root level, then root level A2, then all sub-cats for this root level and so
> on.
<snip>

Have you tried:

SELECT * FROM STOCKCATS
ORDER BY
CASE WHEN level='' THEN catid + '-0'
ELSE level + '-1'
END, catid

hth,

Regards,

Branco.

• Next in forum: Log Invalidated after truncate table
• Prev in forum: stored procedure with array of parameters
• Thread view: Re: What looks like a basic SQL query still not resolved

[Reply to this message]