Make exact match show at top

SELECT * 
FROM client
WHERE identifyingnumber LIKE ‘%86%‘
ORDER BY LEN(identifyingnumber)

SELECT *
FROM
(
SELECT *, 1 AS PRIO FROM client
WHERE identifyingnumber = ‘86‘
UNION
SELECT *, 2 AS PRIO FROM client
WHERE identifyingnumber LIKE ‘%86%‘
AND identifyingnumber <>‘86‘
) AS X
ORDER BY PRIO

SELECT *
FROM client
WHERE identifyingnumber LIKE ‘%86%‘
ORDER BY 
    CASE WHEN identifyingnumber = ‘86‘ THEN 1 
        WHEN identifyingnumber LIKE ‘86%‘ THEN 2 ELSE 3 END

SELECT *, 
    CASE 
        WHEN identifyingnumber = ‘86‘ THEN 1
        WHEN identifyingnumber LIKE ‘86%‘ THEN 2
        ELSE 3
    END AS Rank
FROM client
WHERE identifyingnumber LIKE ‘%86%‘
ORDER BY Rank



ORDER BY identifyingnumber = ‘86‘ DESC

Make exact match show at top

原文：http://www.cnblogs.com/likem/p/4329983.html