From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Unusual sorting requirement (mixed enum/non-enum) - need thoughts |
Date: | 2015-07-03 15:27:29 |
Message-ID: | CAKFQuwYhBAg5Y-2UUz_NL4-VHKumEAQQD-x=Vki32tpB2aPRfg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-07-03 15:36:00 | Re: Unusual sorting requirement (mixed enum/non-enum) - need thoughts |
Previous Message | Adrian Klaver | 2015-07-03 12:52:29 | Re: max number of locks |