Monday, March 12, 2012

Oracle SQL - Order BY question ?

This should be an easy one.
Scenario:
a column can contain 1 of 4 codes "R" "T" "C" "P"

with a one sql select statement how can I order first all "R"s, then "T"s, then "C"s, then "P"s.

when using sql to an Access DB the following Order by works:
ORDER BY
CSFOCP.FORM.TYPE = 'R',
CSFOCP.FORM.TYPE = 'T',
CSFOCP.FORM.TYPE = 'C',
CSFOCP.FORM.TYPE = 'P'

but it doesn't work in Oracle.

Thanks
jimYou might want to throw in a CASE statement

SELECT
YOUR_COLUMN,
CASE YOUR_COLUMN
WHEN 'R' THEN 1
WHEN 'T' THEN 2
WHEN 'C' THEN 3
WHEN 'P' THEN 4
ELSE 5
END AS SORT_ORDER
FROM YOUR_TABLE
ORDER BY 2|||Yes, that worked well. Thank you.

No comments:

Post a Comment