选择具有条件的组的第一行 [英] Select first row of group with criteria

问题描述

我有一个这样的表格:

FieldA   FieldB   FieldC
1111     ABC      X
1111     DEF      Y
1111     GHI      X
2222     JKL      Y
2222     MNO      X
3333     PQR      U
3333     STT      U

我想为每个FieldA选择一个FieldB，优先选择FieldC中的X(如果没有X，则选择另一个).

I want to select one FieldB per FieldA with preference to X in FieldC (if there no X, pick another one).

我尝试将RANK函数与PARTITION BY一起使用，但发现它太不一致了，我现在碰壁了.

I've tried using the RANK function with PARTITION BY but I find it too inconsistent and I have now reached a wall.

我的输出看起来像这样:

My output would look like this:

 FieldA   FieldB   FieldC
    1111     ABC      X
    2222     MNO      X
    3333     PQR      U

查询:

Select
rank() over (partition by Field3 order by Field1),
Field,1 Field2, Field3
FROM table
ORDER BY Field1, Field3

我猜我需要将该查询放在子查询中...

I'm guessing I'd need to put that query inside a sub-query...

推荐答案

您可以像这样使用 ROW_NUMBER :

SELECT FieldA, FieldB, FieldC
FROM (
   SELECT FieldA, FieldB, FieldC,
          ROW_NUMBER() OVER (PARTITION BY FieldA
                             ORDER BY CASE 
                                         WHEN FieldC = 'X' THEN 1
                                         ELSE 2
                                      END,
                                      FieldB) AS rn 
   FROM mytable) AS t
WHERE t.rn = 1

上面的查询从每个 FieldA 分区中选择一条记录.它将具有 FieldC ='X'的记录优先于所有其他记录.

The above query picks one record out of each FieldA partition. It prioritizes records having FieldC = 'X' over all other records.

这篇关于选择具有条件的组的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！