Postgres: Order by string column with known values

Question

I have an events table with a state column. If everything goes according to plan, the states can only be one of:

• scheduled
• invited
• notified
• started
• ended

Is it possible to order by state and specify which value comes first, second, third, etc...?

Bonus Points: Is there a way to do this easily in Rails 3?

Answer 1

1.If you just need a sql in postgres, here it is :

select * from events
order by (case state 
          when 'scheduled' then 1
          when 'notified' then 2
          when 'invited' then 3
          when 'started' then 4
          when 'ended' then 5 
          end)    

you can change the order of states in sql, no need to change the ruby code, play the sql fiddle: http://sqlfiddle.com/#!12/976e9/3.

2.In mu's suggestion, you can use a enum type, it's more efficient, if you need to change the order, you can recreate the enum. see this sql fiddle: http://sqlfiddle.com/#!12/f6f3d/2

CREATE TYPE states AS ENUM ('invited', 'scheduled', 'notified', 'started', 'ended');
create table events(
  name varchar(100),
  state states
);

select * from events order by state;

3.In pure ruby way, you can define a hash:

test_hash = {'scheduled'=>1, 'notified'=>2, 'invited'=>3, 'started'=>4, 'ended'=>5}
Events.all.sort! {|x, y| test_hash[x.state] <=> test_hash[y.state]}

4.But in my opinion, you should add a table named "states", with columns "name" and "sequence", and specify the order in "sequence". Join the "events" and "states" then. When you change the order, you don't need to change the code.