通过在PARTITION BY和ORDER BY子句中使用相同的字段，rn字段将始终等于1.

By using the same field in the PARTITION BY and ORDER BY clause, the rn field will always equal 1.

假设new_registration_no =代码且category = decs，则可以将ORDER BY字段更改为ORDER BY category DESC以获得该结果.但是，这是一个非常随意的ORDER BY-您只是将其基于随机文本值.我也不能100％地确定ROW_NUMBER()函数在CTE中的工作情况.

Assuming that new_registration_no = code and category = decs, you could change the ORDER BY field to be ORDER BY category DESC to get that result. However, that's a pretty arbitrary ORDER BY - you're just basing it on a random text value. I'm also not 100% sure how well the ROW_NUMBER() function works in a CTE.

更好的解决方案可能是:

A better solution might be something like:

SELECT *
FROM 
  (
    SELECT 
        New_Registration_No, 
        Category, 
        ROW_NUMBER() OVER 
          (
            PARTITION BY New_Registration_No 
            ORDER BY 
                CASE 
                    WHEN Category = 'Re-Registration' THEN 1
                    WHEN Category = 'New' THEN 2
                    ELSE 3
                END ASC ) rn
    FROM Equipment_Registrations
  ) s
WHERE rn = 1

您可以在CASE语句中设置所需的顺序-恐怕没有更多信息，这是我能为您提供的***解决方案.如果您知道该字段中可能出现的值列表，则应该很简单；否则，配置起来会有些困难，但这将基于您未包含在原始帖子中的业务规则.

You can set the order in the CASE statement to be whatever you want - I'm afraid that without more information, that's the best solution I can offer you. If you have a known list of values that might appear in that field, it should be easy; if not, it will be a little harder to configure, but that will be based on business rules that you did not include in your original post.