Thread: Unusual sorting requirement (mixed enum/non-enum) - need thoughts

CREATE TYPE enum_type AS ENUM ('X-One','A-Two');

SELECT * 

FROM (VALUES 

('Not Enum'::text, 1::int, 'Uno'::text), 

('Not Enum', 2, 'Dos'), 

('Enum', 4, 'X-One'),

('Enum', 3, 'A-Two')) val (flag, id, val)

;

I need to write an ORDER BY clause that will result in the output of: 1, 2, 4, 3

Basically, if value is capable of being cast to the enum it should be and the sorting order of the enum used; otherwise order on the value of id.

It doesn't matter how the two groups, enums and non-enums, sort relative to each other - for any execution of the query either all values will be enum-able or none will be.

Trying to do this in pure SQL though I'm thinking I'll have to do this in pl/pgsql and put the "is enum-able" check external to the query and either use dynamic SQL or write two separate queries.

I tried casting the enum to an integer but it would not let me :(

Thanks in advanced for any thoughts.

David J.