Case / Order By

How would I change this query that I am running from my servlet so that it takes out the duplicates of B and R just like it is currently doing, but then also combines E and V and removes their duplicates and orders them by E then V? I can add them together with the following code, but cannot order them separately. Any ideas? The order should be B, R, E, V with B and R unioned and E and V unioned but seperated in the order. Currently it is B and R bunched together followed by E and V bunched together. E needs to come before V.

SELECT DISTINCT
a.itemtype
, c.textid
, c.min
, c.max
, rtrim(c.header) as header
, CASE a.itmlabel
WHEN 'B' THEN 1
WHEN 'R' THEN 1
WHEN 'E' THEN 2
WHEN 'V' THEN 2
END ORDERING_COLUMN FROM
RTC_global.productLibrary.vdepts d
, RTC_global.productLibrary.item a
, RTC_global.productLibrary.text c
WHERE
a.priceapproved = 'Y'
AND d.deptcode = -- tempInt.parseInt(dept[0])
AND d.classcode = -- tempInt.parseInt(classCode[0])
AND d.subcode = -- tempInt.parseInt(subCode[0])
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' ORDER BY ORDERING_COLUMN

I think a simple change is all you need:

No, I tried that but it does not work. There are four possible values for itmlabel, 'R', 'B', 'E', and 'V'. There is another field called textid that needs to be distinct, but in some cases there are multiple instances of a textid because it will match up with more than one itmlabel. The textids with itmlabel B and R need to be combined and the textids with itmlabel E and V need to be combined. They then need to be ordered B, R, E, V.


Thanks in advance for the help.

I will need to see your data. Can you put it on a web site or is it too much?

For simplicity, lets say I have a table with these columns:

textid, itmlabel

Then here is some sample data:

111222,B
111222,R
111223,E
111224,V
111233,E
111233,V
111255,R
111266,V
111300,E
111301,E
111301,V
111655,B
111666,B
111666,R

I then need to combine / remove duplicates between B and R and then E and V. I then need to order B, R, E, V.

I don't know what database you are working in, but they should all support temporary tables. I would select from your sample data something like this:

select distinct colA, colB
into #tmpTable <-- this works for MS SQLServer and Sybase
from sampleTable

Then do your join on the temp table with the modified query I sent earlier (you can create indexes on temp tables if you have enough data that it is worth while). Temporary tables often make for much more maintanable code as someone comming after you can clearly see what you intend. It also can produce dramatic improvements in performance, I have seen as much as two orders of magnitude speedup. Temp tables, in case you were not aware, exist only as long as the connection is open (database connection, not recordset connection) so you can reuse them if you like. If you put your query into a stored procedure (my favorite for all but the most simple queries), the temp table disappears as soon as the proc terminates.

I am using DB2 and due to the requirements, will be unable to work with a temporary table. I need one SQL statement that can accomplish the task.

I don't know anything about db2, but in Sybase and MSSqlServer you can create temp tables in regular SQL statments submitted to the server.