asked on

ORDER BY - DB2 SQL

I have a simple SQL, in DB2...

SELET EVAL_CODE
FROM DB2DBA.TEST_CODE

The values of EVAL_CODE are 'T','C','O','P','Z','D'

if I add "ORDER BY EVAL_CODE DESC" or "ORDER BY EVAL_CODE ASC" to the SQL, the result will be like... 'C','D','O','P','T','Z' or 'Z','T','P','O','D','C'

Is there a way that I can order the result set as 'Z','O','P','C','D','T'?

Thanks,

carsRST

You would have to add another field, associate the order you want to the letters, and order by the new field.

EVAL_CODE EVAL_CODE_ORDER (new field)
Z 1
O 2
P 3
(on down)

ASKER CERTIFIED SOLUTION

carsRST

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

k_murli_krishna

Try this:

SELET EVAL_CODE
FROM DB2DBA.TEST_CODE
ORDER BY
CASE EVAL_CODE WHEN 'Z' THEN 1
WHEN 'O' THEN 2
WHEN 'P' THEN 3
WHEN 'C' THEN 4
WHEN 'D' THEN 5
WHEN 'T' THEN 6
END;

You can try give an alias to the CASE using END EVAL_CODE_ORDER OR END AS EVAL_CODE_ORDER.

txman35

ASKER

Thank you very much for all your help. It works great.