Hi Guys

Many thanks for your replies. I must be honest, I've been a naughty poster
and posted this in an ASP ng as well. A genius called David helped me with
the answer, so just for your ref the solution is as follows:

SELECT catid, level
FROM stockcats
ORDER BY COALESCE(NULLIF(level,''),catid),catid;

Rgds Laphan



"Rich Ryan" <ryanrj@sbcglobal.net> wrote in message
news:p78ff.486$4o7.406@newssvr24.news.prodigy.net. ..
It's not so bad. Question: Can a category belong to more than one level? And
thanks for posting the DDL. It makes it much easier.

"Laphan" <info@SpamMeNot.co.uk> wrote in message
news:11nooco13bv9db2@corp.supernews.com...
> ARRRRRRRGGGGGHHHHH!!
>
> Please can you help, I'm going round the bend with this.
>
> I have a simple and small table called STOCKCATS, which I need to query to
> get back a dataset in a particular order, but although it looks simple I
> can't get it to work. My table schema plus sample data to see the problem
> is as follows:
>
> DROP TABLE IF EXISTS `STOCKCATS`;
> CREATE TABLE `STOCKCATS` (
> `CATID` varchar(30) NOT NULL default '',
> `LEVEL` varchar(30) default NULL,
>
> PRIMARY KEY (`CATID`),
> KEY `indxCATEGORYID` (`CATID`)
> );
>
> INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
> ('A001',''),
> ('A002','A001'),
> ('A003','A001'),
> ('A004','A001'),
> ('A005','A001'),
> ('PCHW01',''),
> ('MHW01',''),
> ('FD01',''),
> ('ELEC01',''),
> ('MHW02','MHW01');
> INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
> ('MHW03','MHW01'),
> ('MHW04','MHW01'),
> ('MHW05','MHW01'),
> ('PCHW02','PCHW01'),
> ('PCHW03','PCHW01'),
> ('PCHW04','PCHW01'),
> ('PCHW05','PCHW01'),
> ('PCSW01',''),
> ('MSW01',''),
> ('C001',''),
> ('C002','C001'),
> ('C003','C001'),
> ('MV',''),
> ('SUZ','MV'),
> ('ALF','MV'),
> ('PLASMA','ELEC01'),
> ('T01','ELEC01'),
> ('HEATING',''),
> ('RAD','HEATING'),
> ('P01',''),
> ('B01','P01'),
> ('BB','HEATING'),
> ('FS','HEATING'),
> ('WM','HEATING'),
> ('AEROSOL',''),
> ('SOLVENTS','AEROSOL'),
> ('DGC','');
> INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES
> ('DGXWINDOWS','DGC'),
> ('DGXEXTRA','DGC'),
> ('DGXCON','DGC');
>
> 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. An example using the above would be as follows:
>
> ^ ^ A to G of root level cats plus their sub-cats....
>
> HEATING << root level
> BB << sub-cat of heating
> FS << sub-cat of heating
> WM << sub-cat of heating
>
> \/ \/ I to Z of root level cats plus their sub-cats....
>
> A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),
> CATID and I thought this had done it, but I was looking at the
('A001',''),
> ('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')
> entries as these naturally fell into place. If you use this order command
> on the above you will see that ('P01','') and it's associated
('B01','P01')
> sub-cat just don't come together.
>
> Does any body have any ideas?
>
> Thanks
>
> Laphan
>
>
>