sql - MYSQL Order by with case -

i have product table has following structure.

   productid     productname     producttype    1             irrigation      1      2             landscape       2      3             sleeving        3      4             planting        4   

now need returns rows in order of product type 3,2,4,1 used mysql field method works fine this

   select * product order field(producttype,3,2,4,1) 

this working fine,

my problem if productname empty producttype 3, should take next productname not empty, in such case result order should 2,4,1,3.

so first condition records need in following order of product type

sleeving      3 landscape     2 planting      4 irrigation    1                 

but if productname producttype 3 empty order need be

landscape     2 planting      4 irrigation    1               3 

and further productname producttype 2 empty order need be

planting      4 irrigation    1               3               2 

from result need pick first record.

i hope clear point

any appreciated

this satisfy specification:

order nullif(productname,'') null, field(producttype,3,2,4,1) 

the first expression return 1 if productname "empty" (null or 0 length string'), otherwise return 0.

so, sort non-empty productname first, followed empty productname.

and then, sort original expresssion.

note approach preserves specified order, when there 2 or more (or all) empty productname.

(the test "empty" extended include other cases, example, using trim() function well.

---

the expression

nullif(productname,'') null 

is shorthand, it's equivalent sql-92 compliant:

case when productname = '' or productname null 1 else 0 end  

(and there other ways same result.)