CODEWITHSUNDEEP
sqlpostgresqlsorting
SirRupertIII
SirRupertIII

Reputation: 12595

How can I use postgresql to sort a query with a custom sort order on a string?

I have a table incident that has a status that is a string.

I want to query all incidents with a custom sort order.

A status can be one of the following: inProgress, completed, canceled

I want to be able to have a sort that is custom. Let the client specify the sort order. I am having problems with the query itself though.

I've tried a few things:

SELECT * 
FROM incident as i 
ORDER BY array_position(array["inProgress", "completed", "canceled"], i.status)

SELECT * 
FROM incident as i 
ORDER BY case when status = "inProgress" then 0
         case when status = "completed" then 1
         case when status = "canceled" then 2
              else 3

I get the error Unhandled rejection SequelizeDatabaseError: column "inProgress" does not exist on all of my attempts.

I'm expecting inProgress to be a value of status, but I'm not sure what I'm doing wrong.

Upvotes: 0

Views: 222

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Check documentation for the right sintaxis. And text use single quotes. Double quotes is for fieldnames

ORDER BY case 
              when status = 'inProgress' then 0
              when status = 'completed' then 1
              when status = 'canceled' then 2
              else 3
         end

Upvotes: 2

Related Questions