Menu
My table has three boolean fields: f1, f2, f3. If I do
SELECT * FROM table ORDER BY f1, f2, f3 the records will be sorted by these fields in the order false, true, null. I wish to order them with null in between true and false: the correct order should be true, null, false.
I am using PostgreSQL.
416
In SQL Server, it won't let you use boolean to do ordering, but, you can use boolean statement in combination with case statement… see the example below… If it was to do ordering by IsOdd field, all the even number would show first. Using a case statement, it would show odd numbers 1st.
In standard SQL, a Boolean value can be TRUE , FALSE , or NULL .
select * from table_name where boolean_column is null; works well. Give the result with all transaction having null value for that column.
You can update boolean value using UPDATE command. If you use the BOOLEAN data type, MySQL internally convert it into tinyint(1). It can takes true or false literal in which true indicates 1 to tinyint(1) and false indicates 0 to tinyint(1).
Not beautiful, but should work:
... order by (case when f1 then 1 when f1 is null then 2 else 3 end) asc A better solution would be to use
f1 DESC NULLS LAST
if you're okay with the order true, false, nil ( I guess the important part of your question was, like in my situation now, to have the not-true vaules together)
https://stackoverflow.com/a/7621232/1627888
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
