CODEWITHSUNDEEP
sqlruby-on-railspostgresql
fabriciofreitag
fabriciofreitag

Reputation: 2883

ORDER BY CASE WHEN Alphabetically

I'm trying to order a query based on i18n keys I have on memory. So I built this query:

SELECT  "workflow_tasks".* FROM "workflow_tasks"
ORDER BY CASE
WHEN type='Workflow::Tasks::EmailCloseBrothersAboutPersonalDetailsChange' THEN 'Email Close Brothers'
WHEN type='Workflow::Tasks::CaptureFinanceProposal' THEN 'Record Finance Status'
WHEN type='Workflow::Tasks::SubmitCreditCheck' THEN 'Submit Credit Check'
WHEN type='Workflow::Tasks::UpdateDriverBankDetail' THEN 'Update Driver''s Bank Details'
WHEN type='Workflow::Tasks::UpdateDriverPersonalDetail' THEN 'Update Driver''s Personal Details'
WHEN type='Workflow::Tasks::ValidateInsuranceCertificate' THEN 'Validate Driver''s Insurance Certificate'
END

I expected it to give me an alphabetical list of tasks based on the string that follows THEN

The order is almost correct, except that ValidateInsuranceCertificate comes before UpdateDriverPersonalDetails. I must've misunderstood the usage of CASE in ORDER BY: enter image description here

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I am guessing that you are somehow not getting an exact match. I would start with:

SELECT wf.*,
       (CASE WHEN type = 'Workflow::Tasks::EmailCloseBrothersAboutPersonalDetailsChange' THEN 'Email Close Brothers'
             WHEN type = 'Workflow::Tasks::CaptureFinanceProposal' THEN 'Record Finance Status'
             WHEN type = 'Workflow::Tasks::SubmitCreditCheck' THEN 'Submit Credit Check'
             WHEN type = 'Workflow::Tasks::UpdateDriverBankDetail' THEN 'Update Driver''s Bank Details'
             WHEN type = 'Workflow::Tasks::UpdateDriverPersonalDetail' THEN 'Update Driver''s Personal Details'
             WHEN type = 'Workflow::Tasks::ValidateInsuranceCertificate' THEN 'Validate Driver''s Insurance Certificate'
         END) as sortkey
FROM "workflow_tasks" wf
ORDER BY sortkey;

Upvotes: 1

Nathan Kontny
Nathan Kontny

Reputation: 231

There's a typo in your query describing type.

You have: 

WHEN type='Workflow::Tasks::UpdateDriverBankDetail'

But the type in your database screenshot is plural. So you should have: 

WHEN type='Workflow::Tasks::UpdateDriverBankDetails'

There might be more typos, but the screenshot truncates the data.

Upvotes: 0

Related Questions