SQL for SQLite: Sorting not working with null columns

The following is my sql statement for SQLite:

Select UGM.*, C.Name, C.ChatName From
UserGroupMembers UGM Inner Join Contacts C On C.ContactID = UGM.Membe开发者_如何学GorID
Where (UGM.OwnerID = ?) And (UGM.UserGroupID = ?)
Order By COALESCE(C.Name, C.ChatName) 

The table Contacts contains a Name and ChatName column. If the Name column is not null, then the data from this column is used in the sorting. If it is Null, then the data from the ChatName is to be used instead. Both columns will never be null at the same time.

The sql statement does return the correct rows but it is never sorted properly. Any idea why? Thank you!

You can simply use this :

Select * from (
  Select UGM.*, C.Name, C.ChatName, COALESCE(C.Name, C.ChatName) as Sorting 
    From UserGroupMembers UGM 
    Inner Join Contacts C On C.ContactID = UGM.MemberID
    Where (UGM.OwnerID = ?) And (UGM.UserGroupID = ?))
) Order By Sorting