How to write customer order by in SQL Server

Solution 1:

You can try to use CASE WHEN on ORDER BY

look like this.

SELECT *
FROM myTable
ORDER BY
 createDate DESC,
 CASE 
    WHEN col1 collate Chinese_PRC_CS_AS like '%Tom%' THEN 2
    WHEN col2 like '%apple%' THEN 1
    ELSE 0
 END DESC

Note :

• collate Chinese_PRC_CS_AS care uppercase and lowercase

sqlfiddle:http://sqlfiddle.com/#!18/66fd1/2

Result:

|   col1 |        col2 |           createDate |
|--------|-------------|----------------------|
|  U Tom |   Apple pie | 2018-06-19T00:00:00Z |
|    Tom |  banana pie | 2018-06-19T00:00:00Z |
|  S Amy |   Apple pie | 2018-06-19T00:00:00Z |
|  U tOm |   Apple pie | 2018-06-19T00:00:00Z |
|  k tom | Apple juice | 2018-06-18T00:00:00Z |
| Stanly |   Apple pie | 2018-06-18T00:00:00Z |
|  kevin |  banana pie | 2018-06-18T00:00:00Z |

Solution 2:

Based on your question, a simple answer should be:

SELECT *
FROM myTable
ORDER BY createDate DESC, col1 , upper(col2) 

where upper(col2) allow the sort to be case-insensitive

... but it isn't true only if your data are stored with a case insensitive collation, like sql_latin1_general_cp1_ci_as

A more generic answer is :

SELECT *
FROM myTable
ORDER BY createDate DESC
, col1 COLLATE Latin1_General_BIN 
, col2 COLLATE  sql_latin1_general_cp1_ci_as

=> col1 COLLATE Latin1_General_BIN ensures a case sensitive sort of "col1"

=> col2 COLLATE sql_latin1_general_cp1_ci_as ensures a case INsensitive sort of "col2"