Custom sorting of records in SQL using Case Statement

Sometimes we may have to sort records in such a way that we need some records to be always shown at top. At that cases below query may be helpful.

SELECT *   
FROM  Tablename  
ORDER BY CASE WHEN colname = 'Somevalue1' THEN null  
              WHEN colname = 'Somevalue2' THEN '1'  
              ELSE colname END ASC  

Here sorting is based on column 'colname' .

If 'colname' has value 'Somevalue1', it will be always at top as this query treats that value as null.
Similarly 'colname' having value 'Somevalue2', it will be treated as having value '1' and will be assigned in respective position based on that value while sorting.

Hope this will be helpful!