SQL Server-如何聚合和保留“top”值

我们可以利用 ROW_NUMBER 用一个 CASE 表达式:

SELECT CustomerKey, Status
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerKey
        ORDER BY CASE WHEN Status = 'Retained' THEN 0
                      WHEN Status = 'Closed' THEN 1
                      WHEN Status = 'Opened' THEN 2 END) rn
    FROM yourTable
) t
WHERE rn = 1;

Demo

你可以用 ROW_NUMBER ORDER BY [Account Number] :

 SELECT CustomerKey, Status
    FROM
      (
           SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerKey
           ORDER BY [Account Number] ASC) rn
           FROM Table
      ) t
   WHERE rn = 1;