sort column and 0 come at last with case when

Question

How to sort column and 0 come at last with case when? Below is query:

SELECT iAdminID
     , vFirstName
     , vLastName
     , iRank 
  FROM tbl_admin 
 WHERE eDeleted = 'No' 
   AND eStatus = 'Active' 
 ORDER 
    BY CASE WHEN iAdminID = 1 
            THEN 0 
            ELSE iRank=0 END

Below is result for above query:

But what I want is, first record should be as it is and others should be according to iRank (1,2,3,4,5,6,7,8,0,0,0,0)

Answer 1

A simple method is to use severallevels of sorting.

You could do:

order by
    case when iAdmin = 1 then 0 else 1 end,
    case when iRank = 0 then 1 else 0 end,
    iRank

In MySQL this can also be expressed as follows:

order by 
    (iAdmin = 1) desc,
    iRank = 0,
    iRank

A last option: if you know in advance the upper limit for iRank (say, no iRank will ever be greater than 1000), then you can skip one level:

order by 
    (iAdmin = 1) desc,
    case when iRank = 0 then 1000 else iRank end