Irregular Sort within SQL

[jmennen]

How can I sort this resultset so that a column named itmlabel that corresponds to textid will come in the order 'B', 'R', 'E', 'V'?

SELECT DISTINCT a.itemtype, c.textid, c.min, c.max, rtrim(c.header) as header
FROM ONLINETST.vdepts d, ONLINETST.item a, ONLINETST.text c
WHERE a.priceapproved='Y' AND d.deptcode= 400 AND d.classcode= 12 AND d.subcode= 2 AND a.dept=d.dept AND a.subdept=d.subdept AND a.class=d.class AND a.subclass=d.subclass AND a.textid=c.textid AND c.status='A'

Make sure that duplicate values of textid will not be inserted.
i.e. there are many textid's with more than one itmlabel. We want to only return one textid that corresponds to an itmlabel if a duplicate is found (currently working in SQL).

example data:

itemtype, textid, min, max, header, itmlabel
1, 888222, 3.99, 39.99, gloves, B
1, 888222, 3.99, 39.99, gloves, R
1, 888223, 3.99, 39.99, socks, E
1, 888223, 3.99, 39.99, socks, V
1, 888233, 3.99, 39.99, shoes, E
1, 888423, 3.99, 39.99, laces, V
1, 888113, 3.99, 39.99, coat, B
1, 883113, 3.99, 39.99, hat, R

expected return:
1, 888222, 3.99, 39.99, gloves, B
1, 888113, 3.99, 39.99, coat, B
1, 883113, 3.99, 39.99, hat, R
1, 888223, 3.99, 39.99, socks, E
1, 888233, 3.99, 39.99, shoes, E
1, 888423, 3.99, 39.99, laces, V

I appreciate the help.

 

[gregsimpson]

jmennen,

couldn't you use the CASE statement to set a value for each row dependent on the value in itm label and order by the resultant.

Alternatively join it to a table which looks something like

itm_label Value

B 1
E 3
R 2
V 4

join on the itm_label and order by Value

Cheers
Greg