SQL Server语言 - 按定义的层次结构排序

Question

例如，我有以下表格:

tbody> <<tr>

客户端ID	功能
1234	RE
1234	我们
1234	SP
1234	AG)
6789	AG)
6789	我们

WITH cte AS 
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [CLIENT_ID] ORDER BY (
CASE FUNCTION
WHEN 'SP' THEN 0
WHEN 'AG' THEN 1
ELSE 2
END) ASC) AS rn
,[CLIENT_ID]
, FUNCTION 
FROM ope.stg_client
)
SELECT *
FROM cte
WHERE rn = 1

我能得到想要的输出