Reputation: 107
I am having to type lots of text up here to allow me to post this.
Please ignore this text and see my question below.
I have the following code with a partition:
SELECT rownum,"SRNUM" ,
"SRAnalyzeddate",
"Account",
"U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG",
"U_USD_TOTAL_UNDER_SUBSCRIBED",
"DV_U_BUSINESS_UNIT",
"CUSTOMER_SINCE",
"ContractEnd",
"Segment",
"Industry",
"Territory",
"Vertical",
"FIELD_GEO",
"FIELD_MAJOR_AREA",
"FIELD_AREA"
FROM
(
select SR."NUMBER" AS "SRNUM" ,SR."U_ANALYZED_DATE" AS "SRAnalyzeddate",SR."DV_SALES_ACCOUNT" AS "Account","U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG","U_USD_TOTAL_UNDER_SUBSCRIBED", "DV_U_BUSINESS_UNIT","CUSTOMER_SINCE",
acc."U_NEW_SEGMENT" AS "Segment",
acc."DV_INDUSTRY" As "Industry",
acc."DV_U_ACCOUNT_TERRITORY" As "Territory",
acc."DV_U_VERTICAL" As "Vertical",
ter."DV_U_GEO" AS "FIELD_GEO",
ter."DV_U_MAJOR_AREA" AS "FIELD_MAJOR_AREA",
ter."DV_U_AREA" AS "FIELD_AREA",
MAX("END_DATE") AS "ContractEnd",
ROW_NUMBER() OVER (PARTITION BY SR."NUMBER" ORDER BY SR."DV_SALES_ACCOUNT" DESC) AS rownum
from "SURF_RT"."SALES_ACCOUNT" acc
left join "SURF_RT"."SALES_REQUEST" SR on acc."NAME" = SR."DV_SALES_ACCOUNT"
left join "SURF_RT"."SALES_CONTRACT" con on con."DV_ACCOUNT"=SR."DV_SALES_ACCOUNT"
left join "SURF_RT"."U_SALES_TERRITORY" ter on acc."DV_U_ACCOUNT_TERRITORY" = ter."U_NAME"
JOIN "SURF_RT"."U_SALES_REQUEST_ALERT_LINES" line on sr."NUMBER"=line."DV_U_SALES_REQUEST"
JOIN "SURF_RT"."SALES_PRODUCT" prod on line."U_PRODUCT_CODE"=prod."U_PRODUCT_CODE"
WHERE
"DV_SALES_CATEGORY"='Compliance' AND SR."DV_STATE" NOT IN ('Closed Canceled')
AND YEAR("U_ANALYZED_DATE") = '2020' AND MONTH("U_ANALYZED_DATE") IN ('1','2','3','4')
AND acc."DV_TYPE" NOT IN ('Suspect','Prospect','Inactive','Inactive-Former Customer','Vendor')
GROUP BY SR."NUMBER" ,
SR."U_ANALYZED_DATE",
SR."DV_SALES_ACCOUNT",
"U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG",
"U_USD_TOTAL_UNDER_SUBSCRIBED",
"DV_U_BUSINESS_UNIT",
"CUSTOMER_SINCE",
acc."U_NEW_SEGMENT",
acc."DV_INDUSTRY",
acc."DV_U_ACCOUNT_TERRITORY",
acc."DV_U_VERTICAL",
ter."DV_U_GEO" ,
ter."DV_U_MAJOR_AREA" ,
ter."DV_U_AREA"
ORDER BY SR."DV_SALES_ACCOUNT","SRNUM",
"SRAnalyzeddate"
)
I would like each SRNUM for a single DV_SALES_ACCOUNT to have a different row number like this:
SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM3 = rownum3
SRNUM3 = rownum3
SRNUM3 = rownum3
Instead I am getting this:
SRNUM1 = rownum1
SRNUM1 = rownum2
SRNUM1 = rownum3
SRNUM2 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum3
SRNUM3 = rownum1
SRNUM3 = rownum2
SRNUM3 = rownum3
How can I fix this? Thanks!
I am typing a placeholder right here, because for some reason they think my question is "too much code".
Upvotes: 0
Views: 452
Reputation: 10396
The verbal description of what you want to achieve is different from your expected output. In fact, the output you currently get is what you described.
Looking back at your expected output
SRNUM1 = rownum1 SRNUM1 = rownum1 SRNUM1 = rownum1 SRNUM2 = rownum2 SRNUM2 = rownum2 SRNUM2 = rownum2 SRNUM3 = rownum3 SRNUM3 = rownum3 SRNUM3 = rownum3
it seems that you want to assign each SRNUM
with its own number (per account).
With that insight, it's easy to go back to your SQL and implement that:
[...]
ROW_NUMBER() OVER
(PARTITION BY
SR."DV_SALES_ACCOUNT", SR."NUMBER"
) AS "ACCOUNT_SALES_REQUEST_NUMBER"
[...]
You want to be careful with the choice of alias names. ROWNUM
is a term that is already used in SQL and mixes database technology concerns with your application domain concern.
As an example, I used "ACCOUNT_SALES_REQUEST_NUMBER"
here, which is rather long to type, but at least it says on the tin what it is.
Upvotes: 1