SQL Server CTE ROW_NUMBER() OVER PARTITION based on a value

cuxqih21 于 2023-03-22 发布在其他

关注(0)|答案(1)|浏览(149)

SQL Server T-SQL CTE ROW_NUMBER() OVER PARTITION based on a value

Want numbers based on NAME, VAL1, and VAL2 columns, then order by DT dates descending.

WITH cteA (NAME, VAL1, VAL2, DT) AS 
(
    SELECT 'A', '7100', 'PN1', '2023-03-01' UNION
    SELECT 'A', '7100', 'PN1', '2023-01-01' UNION
    SELECT 'A', '7100', 'PN3', '2022-09-01' UNION
    SELECT 'A', '7100', 'PN1', '2022-05-20' UNION
    SELECT 'A', '7100', 'PN1', '2022-05-09' UNION
    SELECT 'A', '7100', 'PN1', '2022-08-20'
), 
cteB AS 
(
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY NAME, VAL1, VAL2 
                           ORDER BY DT DESC) ROWNUMBER
    FROM cteA
)
SELECT *
FROM cteB
ORDER BY DT DESC

Using the ROW_NUMBER OVER PARTITION BY NAME, VAL1 , and VAL2 but the numbers on the results are not as desired:

NAME	VAL1	VAL2	DT	ROWNUMBER
A	7100	PN1	2023-03-01	1
A	7100	PN1	2023-01-01	2
A	7100	PN3	2022-09-01	1
A	7100	PN1	2022-08-20	3
A	7100	PN1	2022-05-20	4
A	7100	PN1	2022-05-09	5

The numbers need to be separated by VAL2 column: PN1 (first 2 rows before PN3), PN3 itself, & PN1 (last 3 rows after PN3),

and keep DT descending,

I am expecting numbers (ROWNUMBER column) on the result like this:
| NAME | VAL1 | VAL2 | DT | ROWNUMBER |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| A | 7100 | PN1 | 2023-03-01 | 1 |
| A | 7100 | PN1 | 2023-01-01 | 1 |
| A | 7100 | PN3 | 2022-09-01 | 2 |
| A | 7100 | PN1 | 2022-08-20 | 3 |
| A | 7100 | PN1 | 2022-05-20 | 3 |
| A | 7100 | PN1 | 2022-05-09 | 3 |

sql-server

来源：https://stackoverflow.com/questions/75780651/cte-row-number-over-partition-based-on-a-value

1条答案

按热度按时间

guicsvcw1#

This is a variation of a gaps-and-island problem, you need an intermediary step to identify the islands (Val2) which you can do using lag to mark when the value changes, a running total then gets your row number:

with cteA (NAME, VAL1, VAL2, DT) as 
(
  select 'A', '7100', 'PN1', '2023-03-01' union
  select 'A', '7100', 'PN1', '2023-01-01' union
  select 'A', '7100', 'PN3', '2022-09-01' union
  select 'A', '7100', 'PN1', '2022-05-20' union
  select 'A', '7100', 'PN1', '2022-05-09' union
  select 'A', '7100', 'PN1', '2022-08-20'
), 
cteB as 
(
select *,
  case when Lag(val2,1,1) over (partition by NAME, VAL1 order by DT desc) 
   != val2 then 1 
  end gp
from cteA
)
select *, 
  Sum(gp) over(partition by NAME, VAL1 order by DT desc) Rownumber
from cteB
order by DT desc;

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server CTE ROW_NUMBER() OVER PARTITION based on a value

1条答案

相关问题

热门标签

最新问答