Q:

Conditional ordering for Latin and English Names in SQLite

I have a table 'Employee' which has column DevId, Id, FName, FNamePinYin.
FName will have both Chinese and English contact names. Now as per requirement I could manage to get contacts in below order:
FName          FNamePinYin
爱华 杨         AIHUA YANG
安国华          ANGUOHUA
Anguohua       ANGUOHUA
Aihua Yang     AIHUA YANG
爸              BA
波 小           BO BEI BI XIAO
毕慧            BIHUI
Bin Guo        BIN GUO
Bihui          BIHUI
Ba             BA

Using below query:
Select FName, SortString 
from Employee 
where Id in (SELECT Id 
             FROM EMP1
             WHERE '1' = DevId 
             ORDER BY FnamePinYin 
             LIMIT 500 OFFSET 0) 
ORDER BY substr(FnamePinYin,1,1) , Lower(FName) DESC

Now the problem is that contact names are not sorted in ascending order.
Note: Here Lower(FName) DESC is required to get Chinese names to be displayed first in each alphabet's category.
My desired output: 
FName          FNamePinYin
爱华 杨         AIHUA YANG
安国华          ANGUOHUA
Aihua Yang     AIHUA YANG
Anguohua       ANGUOHUA
爸             BA
波 小           BO BEI BI XIAO
毕慧            BIHUI
Ba             BA
Bihui          BIHUI
Bin Guo        BIN GUO

FNamePinYin is English equivalent of Chinese names.
Can anyone help me get the result I want?

A:

Your question is still not entirely clear, but based on your statement that "both Chinese and English Name should be in Acceding order" "in each category (A-Z)", then the following should do what I think you want:
 select * 
 from Employee 
 where Id in (SELECT Id 
         FROM PBAPL1 
         WHERE '1' = DevId 
         ORDER BY FnamePinYin 
         LIMIT 500 OFFSET 0)
 order by substr(FNamePinYin,1,1), (substr(FName,1,1) < 'zz'), FName;

