admin 管理员组

文章数量: 1086019
NodeId NodePath ProcessingSystem
55315710 ALL->Test->Sub test1
55315710 ALL->Test->Sub test2
109389601 ALL->Path2->Sub test2
109389601 ALL->Path2->Sub test1
99315710 ALL->Test->Sub test1
NodeId NodePath ProcessingSystem
55315710 ALL->Test->Sub test1
55315710 ALL->Test->Sub test2
109389601 ALL->Path2->Sub test2
109389601 ALL->Path2->Sub test1
99315710 ALL->Test->Sub test1

From the data set shown here, for a node id, if value test1 exists, then we should fetch only that row else if all are having value test2 then fetch the top 1 since node id and path is always same

  • sql
  • sql-server
Share Improve this question edited Mar 28 at 7:57 Thom A 96.3k11 gold badges61 silver badges95 bronze badges asked Mar 28 at 4:32 edwin josephedwin joseph 214 bronze badges 1
  • 2 Please include sample output and clearly explain the logic. – Tim Biegeleisen Commented Mar 28 at 4:38
Add a comment  | 

1 Answer 1

Reset to default 1

Use a window function to tag each row with its ordered position against its siblings (those having the same NodeID),
then select only those having a position of 1 within their group;
the "then" can either be implemented as a subselect or a Common Table Expression, as I do here (with the with … as that defines a virtual temporary table tpos):

with tpos as
(
    select
        *,
        row_number() over (partition by NodeId order by ProcessingSystem) pos -- ← There you can change the order criteria in case you've got other columns to decide on.
    from t
)
select * from tpos where pos = 1;
NodeId NodePath ProcessingSystem pos
9999 ONLY->A->test2 test2 1
55315710 ALL->Test->Sub test1 1
99315710 ALL->Test->Sub test1 1
109389601 ALL->Path2->Sub test1 1

(play with it in this fiddle)

Note that row_number() ensures you will always have only one 1, even in case of full duplicates.

本文标签： sqlFilter values from a resultset based on column valueStack Overflow

版权声明：本文标题：sql - Filter values from a resultset based on column value - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1744056891a2526014.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。