mysql - What is the best way to get sorted SQL query based on null values -

i have table in database want extract data in specific order.
here example of table:

create table `test` (   `field1` int(11) not null,   `field2` int(11) default null,   `field3` int(11) default null,   unique key `field1_2` (`field1`,`field2`),   unique key `field1_3` (`field1`,`field3`), ) engine=innodb default charset=utf8; 

valid entries in table is:

• field1 must filled
• field2 can either null or unique value based on field1 , field2
• field3 can either null or uniqie value based on field1 , field3

the order want extract data is:

• all fields field2 , field3 null
• all fields field2 null , field3 values
• all fields field3 null , field2 values

so far have used 3 queries connected union

select * test field2 null      , field3 null union select * test field2 null      , field3 not null union  select * test field2 not null      , field3 null; 

i myself feel necessary code in order , hoped there better solution.

so there better way?

just put conditions single order by:

order (field2 null , field3 null) desc,          (field2 null , field3 not null) desc,          (field2 null , field3 not null) desc 

you might want add where well:

where field2 null or field3 null 

this works because mysql treats booleans numbers in numeric context, "true" 1 , "false" 0. why desc needed. can express same thing in standard sql as:

order (case when field2 null , field3 null 1                when field2 null , field3 not null 2                when field2 not null , field3 null 3                else 4           end)