I believe that you could define an enumerated type to use for those status colors such that the ordering is defined as you like without two separate columns for the name and sort_value or whatever.
Example in the documentation expanded a little to demonstrate-
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
INSERT INTO person VALUES ('Joe', 'sad');
INSERT INTO person VALUES ('Roe', 'ok');
SELECT * FROM person order by current_mood;
SELECT * FROM person order by current_mood desc;
Note- using enum may complicate other things in your usage, so I am not suggesting this is ideal, just one option.
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
INSERT INTO person VALUES ('Joe', 'sad');
INSERT INTO person VALUES ('Roe', 'ok');
SELECT * FROM person order by current_mood;
SELECT * FROM person order by current_mood desc;
Michael Lewis | Database Engineer
Entrata
