CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordruby-on-rails-5
Juan David Rodriguez
Juan David Rodriguez

Reputation: 1

Order by case with NULLs not first nor last

I'm trying to order a query by an enum, where 2 values of my enum i want to have the same priority, but also i want NULLs to be mixed in there, rather than first.

I'm using a case when syntax with OR operators to concat the same level of priority between them, it seems to work just fine except for the NULLs, which aren't properly ordered in, they seem random.

CASE
  WHEN company_size = 0 OR
   company_size = 1 OR
   company_size = NULL THEN '0'
  WHEN company_size = 2 THEN '1'
  WHEN company_size = 3 THEN '2'
END

Help please

Upvotes: 0

Views: 46

Answers (2)

jjanes
jjanes

Reputation: 44287

NULL = NULL evaluates to NULL, not to true. In a CASE WHEN expression, NULL has the same outcome as false does. To mingle the NULL in with the 1 and 0, You probably want this:

CASE
  WHEN company_size = 0 OR
   company_size = 1 OR
   company_size is NULL THEN '0'
  WHEN company_size = 2 THEN '1'
  WHEN company_size = 3 THEN '2'
END

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

You can use order by coalesce()

select * from tableA
order by coalesce(company_size, 0)

Upvotes: 1

Related Questions