在CTE中使用ROW_NUMBER [英] Using ROW_NUMBER in CTE

问题描述

我正在尝试使用ROW_NUMBER函数的CTE。

查询：

 ，Demo为
（
 select *，ROW_NUMBER（）超过（按GID分区，按MMID按MID顺序）作为Table1中的ROWNUMBER，其中DateEntered>'2015-06-13 00 00：00.000'
）
 select * from Demo 
  

这里，结果我得到的是

  GID MID ROWNUMBER 
 1 1 1 
 1 2 1 
 1 2 2 
 1 2 3 
 2 1 1 
 2 2 1 
 2 2 2 
 2 3 5 
 2 4 4 
  
 
 
简单来说，请获取具有最大行号值的行，如果gid的组合，mid具有rownumber = 1。
 
 
 
我想要的输出是
  GID中间的行号
 1 1 1 
 1 2 3 
 2 1 1 
 2 2 2 
  
我希望我没有使它变得复杂。有人可以告诉我如何执行此操作吗？
解决方案
 
 （演示为$ b） $ b（
 select *，ROW_NUMBER（）over（按GID划分，按MID订购MID）作为RN 
从Table1那里，DateEntered>'2015-06-13 00：00：00.000'
）
，x作为
（选择gid，演示
中的
，其中RN = 1 
）
选择demo.gid，demo.mid ，max（demo.rn）作为demo的rownumb 
的左联接x 
在x.gid = demo.gid和x.mid = demo.mid 
上由demo.gid，demo。中; 
  
您可以使用 max 选择最高每个中段组合的行号。
 
I am trying for a CTE with a ROW_NUMBER function.

Query:
with Demo as
(
select *, ROW_NUMBER() over (partition by GID, MID order by MMID) as ROWNUMBER  from Table1 where DateEntered > '2015-06-13 00:00:00.000'
)
select  * from Demo 
Here, the result I get is
GID   MID   ROWNUMBER
1      1      1
1      2      1
1      2      2
1      2      3 
2      1      1
2      2      1
2      2      2
2      3      5
2      4      4
Now, I want to get all the rows where combination of GID,MID has max row number value. But a condition here is that for those rows, the combination of GID,MID should also have 1.

In simple terms, get me the rows with max row number value, if that combination of gid,mid has rownumber=1.

The output I desire is
GID   MID   ROWNUMBER
1      1      1
1      2      3 
2      1      1
2      2      2
I hope i did not made it complex. Can anyone pls inform me on how to do this ?
解决方案
with Demo as
(
select *, ROW_NUMBER() over (partition by GID, MID order by MMID) as RN  
from Table1 where DateEntered > '2015-06-13 00:00:00.000'
)
, x as 
(select gid, mid
from demo 
where RN = 1
)
select demo.gid, demo.mid, max(demo.rn) as rownumb
from demo left join x
on x.gid = demo.gid and x.mid = demo.mid
group by demo.gid, demo.mid;
You can use max to select the highest rownumber per mid, gid combination.

                        
这篇关于在CTE中使用ROW_NUMBER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！