oracle - no partition in window function but fill sequential numbers for acd properties
I have acd properties table with 3 columns - id, acd and rpt. The rpt is set to 1 when it is first reported for the acd property, but if any consequent acd properties are repeating, it is set to 0. The id column is always incrementing (sort of pk). Now for the continous zeros, I need the sequential numbers starting from 2,3... as shown in the wanted column.
id acd rpt wanted
1 a 1 1
2 b 1 1
3 b 0 2
4 a 1 1
5 a 0 2
6 a 0 3
7 d 1 1
8 d 0 2
9 d 0 3
10 c 1 1
11 c 0 2
12 c 0 3
13 c 0 4
14 c 0 5
15 d 1 1
16 a 1 1
I tried the window function, but when I use "value" column in partition clause it is grouping all a's which is not desired. Is it possible to get the results as in "wanted" column given rpt and id incrementing.
sql oracle
asked Nov 20 at 21:35
stack0114106
1,9651416
add a comment |
I have acd properties table with 3 columns - id, acd and rpt. The rpt is set to 1 when it is first reported for the acd property, but if any consequent acd properties are repeating, it is set to 0. The id column is always incrementing (sort of pk). Now for the continous zeros, I need the sequential numbers starting from 2,3... as shown in the wanted column.
id acd rpt wanted
1 a 1 1
2 b 1 1
3 b 0 2
4 a 1 1
5 a 0 2
6 a 0 3
7 d 1 1
8 d 0 2
9 d 0 3
10 c 1 1
11 c 0 2
12 c 0 3
13 c 0 4
14 c 0 5
15 d 1 1
16 a 1 1
I tried the window function, but when I use "value" column in partition clause it is grouping all a's which is not desired. Is it possible to get the results as in "wanted" column given rpt and id incrementing.
sql oracle
asked Nov 20 at 21:35
stack0114106
1,9651416
is there a column to specify ordering?
– Vamsi Prabhala
Nov 20 at 21:36
updated with id column
– stack0114106
Nov 20 at 21:39
add a comment |
I have acd properties table with 3 columns - id, acd and rpt. The rpt is set to 1 when it is first reported for the acd property, but if any consequent acd properties are repeating, it is set to 0. The id column is always incrementing (sort of pk). Now for the continous zeros, I need the sequential numbers starting from 2,3... as shown in the wanted column.
id acd rpt wanted
1 a 1 1
2 b 1 1
3 b 0 2
4 a 1 1
5 a 0 2
6 a 0 3
7 d 1 1
8 d 0 2
9 d 0 3
10 c 1 1
11 c 0 2
12 c 0 3
13 c 0 4
14 c 0 5
15 d 1 1
16 a 1 1
I tried the window function, but when I use "value" column in partition clause it is grouping all a's which is not desired. Is it possible to get the results as in "wanted" column given rpt and id incrementing.
sql oracle
asked Nov 20 at 21:35
stack0114106
1,9651416
I have acd properties table with 3 columns - id, acd and rpt. The rpt is set to 1 when it is first reported for the acd property, but if any consequent acd properties are repeating, it is set to 0. The id column is always incrementing (sort of pk). Now for the continous zeros, I need the sequential numbers starting from 2,3... as shown in the wanted column.
id acd rpt wanted
1 a 1 1
2 b 1 1
3 b 0 2
4 a 1 1
5 a 0 2
6 a 0 3
7 d 1 1
8 d 0 2
9 d 0 3
10 c 1 1
11 c 0 2
12 c 0 3
13 c 0 4
14 c 0 5
15 d 1 1
16 a 1 1
I tried the window function, but when I use "value" column in partition clause it is grouping all a's which is not desired. Is it possible to get the results as in "wanted" column given rpt and id incrementing.
sql oracle
sql oracle
asked Nov 20 at 21:35
stack0114106
1,9651416
asked Nov 20 at 21:35
stack0114106
1,9651416
edited Nov 20 at 21:39
asked Nov 20 at 21:35
stack0114106
1,9651416
asked Nov 20 at 21:35
stack0114106
1,9651416
asked Nov 20 at 21:35
stack0114106
1,9651416
1,9651416
is there a column to specify ordering?
– Vamsi Prabhala
Nov 20 at 21:36
updated with id column
– stack0114106
Nov 20 at 21:39
add a comment |
is there a column to specify ordering?
– Vamsi Prabhala
Nov 20 at 21:36
updated with id column
– stack0114106
Nov 20 at 21:39
is there a column to specify ordering?
– Vamsi Prabhala
Nov 20 at 21:36
is there a column to specify ordering?
– Vamsi Prabhala
Nov 20 at 21:36
updated with id column
– stack0114106
Nov 20 at 21:39
updated with id column
– stack0114106
Nov 20 at 21:39
add a comment |
2 Answers
2
active
oldest
votes
You need nested OLAP-funtions:
SELECT dt.*,
Row_Number() Over (PARTITION BY grp ORDER BY id)
FROM
( -- calculate a group number using a Cumulative Sum over 0/1 (for partitioning in next step)
SELECT prop.*, Sum(rpt) Over (ORDER BY id ROWS Unbounded Preceding) AS grp
FROM prop
) dt
answered Nov 20 at 22:12
dnoeth
44.5k31838
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
add a comment |
When rpt = 1, then you want 1. Then you want the 0s enumerated for each acd. If this is correct, then the logic is:
select t.*,
(case when rpt = 1 then 1
else 1 + row_number() over (partition by acd, rpt order by id)
end) as wanted
from t;
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53401924%2foracle-no-partition-in-window-function-but-fill-sequential-numbers-for-acd-pro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need nested OLAP-funtions:
SELECT dt.*,
Row_Number() Over (PARTITION BY grp ORDER BY id)
FROM
( -- calculate a group number using a Cumulative Sum over 0/1 (for partitioning in next step)
SELECT prop.*, Sum(rpt) Over (ORDER BY id ROWS Unbounded Preceding) AS grp
FROM prop
) dt
answered Nov 20 at 22:12
dnoeth
44.5k31838
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
add a comment |
You need nested OLAP-funtions:
SELECT dt.*,
Row_Number() Over (PARTITION BY grp ORDER BY id)
FROM
( -- calculate a group number using a Cumulative Sum over 0/1 (for partitioning in next step)
SELECT prop.*, Sum(rpt) Over (ORDER BY id ROWS Unbounded Preceding) AS grp
FROM prop
) dt
answered Nov 20 at 22:12
dnoeth
44.5k31838
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
add a comment |
You need nested OLAP-funtions:
SELECT dt.*,
Row_Number() Over (PARTITION BY grp ORDER BY id)
FROM
( -- calculate a group number using a Cumulative Sum over 0/1 (for partitioning in next step)
SELECT prop.*, Sum(rpt) Over (ORDER BY id ROWS Unbounded Preceding) AS grp
FROM prop
) dt
answered Nov 20 at 22:12
dnoeth
44.5k31838
You need nested OLAP-funtions:
SELECT dt.*,
Row_Number() Over (PARTITION BY grp ORDER BY id)
FROM
( -- calculate a group number using a Cumulative Sum over 0/1 (for partitioning in next step)
SELECT prop.*, Sum(rpt) Over (ORDER BY id ROWS Unbounded Preceding) AS grp
FROM prop
) dt
answered Nov 20 at 22:12
dnoeth
44.5k31838
answered Nov 20 at 22:12
dnoeth
44.5k31838
answered Nov 20 at 22:12
dnoeth
44.5k31838
answered Nov 20 at 22:12
dnoeth
44.5k31838
44.5k31838
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
add a comment |
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
yes.. this works perfect..
– stack0114106
Nov 20 at 22:22
add a comment |
When rpt = 1, then you want 1. Then you want the 0s enumerated for each acd. If this is correct, then the logic is:
select t.*,
(case when rpt = 1 then 1
else 1 + row_number() over (partition by acd, rpt order by id)
end) as wanted
from t;
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
add a comment |
When rpt = 1, then you want 1. Then you want the 0s enumerated for each acd. If this is correct, then the logic is:
select t.*,
(case when rpt = 1 then 1
else 1 + row_number() over (partition by acd, rpt order by id)
end) as wanted
from t;
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
add a comment |
When rpt = 1, then you want 1. Then you want the 0s enumerated for each acd. If this is correct, then the logic is:
select t.*,
(case when rpt = 1 then 1
else 1 + row_number() over (partition by acd, rpt order by id)
end) as wanted
from t;
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
When rpt = 1, then you want 1. Then you want the 0s enumerated for each acd. If this is correct, then the logic is:
select t.*,
(case when rpt = 1 then 1
else 1 + row_number() over (partition by acd, rpt order by id)
end) as wanted
from t;
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
edited Nov 21 at 3:13
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
answered Nov 20 at 21:38
Gordon Linoff
755k35290398
755k35290398
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
add a comment |
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
it should be (2) i.e row_number()+1 when id=5 and acd=a
– stack0114106
Nov 20 at 21:55
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
@stack0114106 . . . Yes, I think the logic you want is 1 + row number.
– Gordon Linoff
Nov 21 at 3:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53401924%2foracle-no-partition-in-window-function-but-fill-sequential-numbers-for-acd-pro%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
is there a column to specify ordering?
– Vamsi Prabhala
Nov 20 at 21:36
updated with id column
– stack0114106
Nov 20 at 21:39