Mixing
Conditional ROW_NUMBER() to skip nulls still counting them
I am trying to add a ROW_NUMBER column to a data set that follows the order of [Withdrawal then Fee; Withdrawal then Fee] based upon whether the column NETWORKCD is null. However, my row_number formula is still counting Nulls even though it's not displaying the numbers, like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 3 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 5 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
I am using the following formula for PeriodCount
(case when networkcd is not null
then row_number() over (partition by acctid order by postdate)
end) PeriodCount
I was expecting PeriodCount to count [1,2,3] instead of [1,3,5], like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 2 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 3 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
What am I missing?
sql plsql oracle11g
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
asked Nov 19 '18 at 15:33
sleven
225
add a comment |
I am trying to add a ROW_NUMBER column to a data set that follows the order of [Withdrawal then Fee; Withdrawal then Fee] based upon whether the column NETWORKCD is null. However, my row_number formula is still counting Nulls even though it's not displaying the numbers, like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 3 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 5 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
I am using the following formula for PeriodCount
(case when networkcd is not null
then row_number() over (partition by acctid order by postdate)
end) PeriodCount
I was expecting PeriodCount to count [1,2,3] instead of [1,3,5], like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 2 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 3 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
What am I missing?
sql plsql oracle11g
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
asked Nov 19 '18 at 15:33
sleven
225
The row_number() is exactly that in this instance, the row number, whether you choose to display it or replace it with a null in the case statement, does not alter which row number it is.
– Andrew
Nov 19 '18 at 15:35
@Andrew That was my first thought, but doesn't the case statement for networkcd is not null adjust for that?
– sleven
Nov 19 '18 at 15:39
Afraid not, Dense_Rank as Alex has posted is the way forward, subtle difference in how it works
– Andrew
Nov 19 '18 at 15:42
add a comment |
I am trying to add a ROW_NUMBER column to a data set that follows the order of [Withdrawal then Fee; Withdrawal then Fee] based upon whether the column NETWORKCD is null. However, my row_number formula is still counting Nulls even though it's not displaying the numbers, like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 3 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 5 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
I am using the following formula for PeriodCount
(case when networkcd is not null
then row_number() over (partition by acctid order by postdate)
end) PeriodCount
I was expecting PeriodCount to count [1,2,3] instead of [1,3,5], like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 2 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 3 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
What am I missing?
sql plsql oracle11g
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
asked Nov 19 '18 at 15:33
sleven
225
I am trying to add a ROW_NUMBER column to a data set that follows the order of [Withdrawal then Fee; Withdrawal then Fee] based upon whether the column NETWORKCD is null. However, my row_number formula is still counting Nulls even though it's not displaying the numbers, like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 3 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 5 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
I am using the following formula for PeriodCount
(case when networkcd is not null
then row_number() over (partition by acctid order by postdate)
end) PeriodCount
I was expecting PeriodCount to count [1,2,3] instead of [1,3,5], like so:
+--------+-------------+------------+-----------+-------------+
| Acctid | Transaction | PostDate | NetworkCd | PeriodCount |
+--------+-------------+------------+-----------+-------------+
| 12345 | Withdrawal | 10/4/2018 | FRGN | 1 |
| 12345 | Fee | 10/4/2018 | | |
| 12345 | Withdrawal | 10/11/2018 | FRGN | 2 |
| 12345 | Fee | 10/11/2018 | | |
| 12345 | Withdrawal | 10/22/2018 | FRGN | 3 |
| 12345 | Fee | 10/22/2018 | | |
+--------+-------------+------------+-----------+-------------+
What am I missing?
sql plsql oracle11g
sql plsql oracle11g
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
asked Nov 19 '18 at 15:33
sleven
225
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
asked Nov 19 '18 at 15:33
sleven
225
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
edited Nov 19 '18 at 15:35
Juan Carlos Oropeza
36k63876
36k63876
asked Nov 19 '18 at 15:33
sleven
225
asked Nov 19 '18 at 15:33
sleven
225
asked Nov 19 '18 at 15:33
sleven
225
225
The row_number() is exactly that in this instance, the row number, whether you choose to display it or replace it with a null in the case statement, does not alter which row number it is.
– Andrew
Nov 19 '18 at 15:35
@Andrew That was my first thought, but doesn't the case statement for networkcd is not null adjust for that?
– sleven
Nov 19 '18 at 15:39
Afraid not, Dense_Rank as Alex has posted is the way forward, subtle difference in how it works
– Andrew
Nov 19 '18 at 15:42
add a comment |
The row_number() is exactly that in this instance, the row number, whether you choose to display it or replace it with a null in the case statement, does not alter which row number it is.
– Andrew
Nov 19 '18 at 15:35
@Andrew That was my first thought, but doesn't the case statement for networkcd is not null adjust for that?
– sleven
Nov 19 '18 at 15:39
Afraid not, Dense_Rank as Alex has posted is the way forward, subtle difference in how it works
– Andrew
Nov 19 '18 at 15:42
The row_number() is exactly that in this instance, the row number, whether you choose to display it or replace it with a null in the case statement, does not alter which row number it is.
– Andrew
Nov 19 '18 at 15:35
The row_number() is exactly that in this instance, the row number, whether you choose to display it or replace it with a null in the case statement, does not alter which row number it is.
– Andrew
Nov 19 '18 at 15:35
@Andrew That was my first thought, but doesn't the case statement for networkcd is not null adjust for that?
– sleven
Nov 19 '18 at 15:39
@Andrew That was my first thought, but doesn't the case statement for networkcd is not null adjust for that?
– sleven
Nov 19 '18 at 15:39
Afraid not, Dense_Rank as Alex has posted is the way forward, subtle difference in how it works
– Andrew
Nov 19 '18 at 15:42
Afraid not, Dense_Rank as Alex has posted is the way forward, subtle difference in how it works
– Andrew
Nov 19 '18 at 15:42
add a comment |
3 Answers
3
active
oldest
votes
It is still showing a value the 1st, 3rd and 5th rows - so the row count as it encounters each of those rows is correct. It's the row number within the entire result set, not within the not-null values. Your case expression is deciding whether to show the value, not affecting how it is found.
You could get the result you want using dense_rank() instead;
with your_table (Acctid, Transaction, PostDate, NetworkCd) as (
select 12345, 'Withdrawal', to_date('10/4/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/4/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/11/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/11/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/22/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/22/2018', 'MM/DD/YYYY'), null from dual
)
select Acctid, Transaction, PostDate, NetworkCd,
(case when networkcd is not null then dense_rank() over (partition by acctid order by postdate) end) as periodcount
from your_table;
ACCTID TRANSACTIO POSTDATE NETW PERIODCOUNT
---------- ---------- ---------- ---- -----------
12345 Withdrawal 2018-10-04 FRGN 1
12345 Fee 2018-10-04
12345 Withdrawal 2018-10-11 FRGN 2
12345 Fee 2018-10-11
12345 Withdrawal 2018-10-22 FRGN 3
12345 Fee 2018-10-22
... as that does suppress gaps in the generated values. From the docs:
DENSE_RANKcomputes the rank of a row in an ordered group of rows and returns the rank as aNUMBER. The ranks are consecutive integers beginning with 1. ...
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
add a comment |
ROW_NUMBER() function doesn't work as you expected, but you can do instead :
select t.*,
(select count(*)
from table t1
where t1.acctid = t.acctid and t1.PostDate <= t.PostDate and
t1.networkcd is not null
) as PeriodCount
from table t;
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
add a comment |
It looks like instead of being NULL it is an empty string. SQL will regard that differently in the code.
Try:
case when networkcd = '' then row_number()....
answered Nov 19 '18 at 15:49
MapinTX
106
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with= ''is the same as= null, which is 'unknown'.
– Alex Poole
Nov 20 '18 at 17:07
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%2f53377925%2fconditional-row-number-to-skip-nulls-still-counting-them%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
It is still showing a value the 1st, 3rd and 5th rows - so the row count as it encounters each of those rows is correct. It's the row number within the entire result set, not within the not-null values. Your case expression is deciding whether to show the value, not affecting how it is found.
You could get the result you want using dense_rank() instead;
with your_table (Acctid, Transaction, PostDate, NetworkCd) as (
select 12345, 'Withdrawal', to_date('10/4/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/4/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/11/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/11/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/22/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/22/2018', 'MM/DD/YYYY'), null from dual
)
select Acctid, Transaction, PostDate, NetworkCd,
(case when networkcd is not null then dense_rank() over (partition by acctid order by postdate) end) as periodcount
from your_table;
ACCTID TRANSACTIO POSTDATE NETW PERIODCOUNT
---------- ---------- ---------- ---- -----------
12345 Withdrawal 2018-10-04 FRGN 1
12345 Fee 2018-10-04
12345 Withdrawal 2018-10-11 FRGN 2
12345 Fee 2018-10-11
12345 Withdrawal 2018-10-22 FRGN 3
12345 Fee 2018-10-22
... as that does suppress gaps in the generated values. From the docs:
DENSE_RANKcomputes the rank of a row in an ordered group of rows and returns the rank as aNUMBER. The ranks are consecutive integers beginning with 1. ...
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
add a comment |
It is still showing a value the 1st, 3rd and 5th rows - so the row count as it encounters each of those rows is correct. It's the row number within the entire result set, not within the not-null values. Your case expression is deciding whether to show the value, not affecting how it is found.
You could get the result you want using dense_rank() instead;
with your_table (Acctid, Transaction, PostDate, NetworkCd) as (
select 12345, 'Withdrawal', to_date('10/4/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/4/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/11/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/11/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/22/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/22/2018', 'MM/DD/YYYY'), null from dual
)
select Acctid, Transaction, PostDate, NetworkCd,
(case when networkcd is not null then dense_rank() over (partition by acctid order by postdate) end) as periodcount
from your_table;
ACCTID TRANSACTIO POSTDATE NETW PERIODCOUNT
---------- ---------- ---------- ---- -----------
12345 Withdrawal 2018-10-04 FRGN 1
12345 Fee 2018-10-04
12345 Withdrawal 2018-10-11 FRGN 2
12345 Fee 2018-10-11
12345 Withdrawal 2018-10-22 FRGN 3
12345 Fee 2018-10-22
... as that does suppress gaps in the generated values. From the docs:
DENSE_RANKcomputes the rank of a row in an ordered group of rows and returns the rank as aNUMBER. The ranks are consecutive integers beginning with 1. ...
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
add a comment |
It is still showing a value the 1st, 3rd and 5th rows - so the row count as it encounters each of those rows is correct. It's the row number within the entire result set, not within the not-null values. Your case expression is deciding whether to show the value, not affecting how it is found.
You could get the result you want using dense_rank() instead;
with your_table (Acctid, Transaction, PostDate, NetworkCd) as (
select 12345, 'Withdrawal', to_date('10/4/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/4/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/11/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/11/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/22/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/22/2018', 'MM/DD/YYYY'), null from dual
)
select Acctid, Transaction, PostDate, NetworkCd,
(case when networkcd is not null then dense_rank() over (partition by acctid order by postdate) end) as periodcount
from your_table;
ACCTID TRANSACTIO POSTDATE NETW PERIODCOUNT
---------- ---------- ---------- ---- -----------
12345 Withdrawal 2018-10-04 FRGN 1
12345 Fee 2018-10-04
12345 Withdrawal 2018-10-11 FRGN 2
12345 Fee 2018-10-11
12345 Withdrawal 2018-10-22 FRGN 3
12345 Fee 2018-10-22
... as that does suppress gaps in the generated values. From the docs:
DENSE_RANKcomputes the rank of a row in an ordered group of rows and returns the rank as aNUMBER. The ranks are consecutive integers beginning with 1. ...
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
It is still showing a value the 1st, 3rd and 5th rows - so the row count as it encounters each of those rows is correct. It's the row number within the entire result set, not within the not-null values. Your case expression is deciding whether to show the value, not affecting how it is found.
You could get the result you want using dense_rank() instead;
with your_table (Acctid, Transaction, PostDate, NetworkCd) as (
select 12345, 'Withdrawal', to_date('10/4/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/4/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/11/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/11/2018', 'MM/DD/YYYY'), null from dual
union all select 12345, 'Withdrawal', to_date('10/22/2018', 'MM/DD/YYYY'), 'FRGN' from dual
union all select 12345, 'Fee', to_date('10/22/2018', 'MM/DD/YYYY'), null from dual
)
select Acctid, Transaction, PostDate, NetworkCd,
(case when networkcd is not null then dense_rank() over (partition by acctid order by postdate) end) as periodcount
from your_table;
ACCTID TRANSACTIO POSTDATE NETW PERIODCOUNT
---------- ---------- ---------- ---- -----------
12345 Withdrawal 2018-10-04 FRGN 1
12345 Fee 2018-10-04
12345 Withdrawal 2018-10-11 FRGN 2
12345 Fee 2018-10-11
12345 Withdrawal 2018-10-22 FRGN 3
12345 Fee 2018-10-22
... as that does suppress gaps in the generated values. From the docs:
DENSE_RANKcomputes the rank of a row in an ordered group of rows and returns the rank as aNUMBER. The ranks are consecutive integers beginning with 1. ...
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
edited Nov 19 '18 at 15:47
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
answered Nov 19 '18 at 15:40
Alex Poole
129k6101176
129k6101176
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
add a comment |
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
Works perfectly, thank you. I'll spend some time looking into Dense_rank().
– sleven
Nov 19 '18 at 15:47
add a comment |
ROW_NUMBER() function doesn't work as you expected, but you can do instead :
select t.*,
(select count(*)
from table t1
where t1.acctid = t.acctid and t1.PostDate <= t.PostDate and
t1.networkcd is not null
) as PeriodCount
from table t;
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
add a comment |
ROW_NUMBER() function doesn't work as you expected, but you can do instead :
select t.*,
(select count(*)
from table t1
where t1.acctid = t.acctid and t1.PostDate <= t.PostDate and
t1.networkcd is not null
) as PeriodCount
from table t;
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
add a comment |
ROW_NUMBER() function doesn't work as you expected, but you can do instead :
select t.*,
(select count(*)
from table t1
where t1.acctid = t.acctid and t1.PostDate <= t.PostDate and
t1.networkcd is not null
) as PeriodCount
from table t;
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
ROW_NUMBER() function doesn't work as you expected, but you can do instead :
select t.*,
(select count(*)
from table t1
where t1.acctid = t.acctid and t1.PostDate <= t.PostDate and
t1.networkcd is not null
) as PeriodCount
from table t;
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
answered Nov 19 '18 at 15:39
Yogesh Sharma
28.3k51335
28.3k51335
add a comment |
add a comment |
It looks like instead of being NULL it is an empty string. SQL will regard that differently in the code.
Try:
case when networkcd = '' then row_number()....
answered Nov 19 '18 at 15:49
MapinTX
106
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with= ''is the same as= null, which is 'unknown'.
– Alex Poole
Nov 20 '18 at 17:07
add a comment |
It looks like instead of being NULL it is an empty string. SQL will regard that differently in the code.
Try:
case when networkcd = '' then row_number()....
answered Nov 19 '18 at 15:49
MapinTX
106
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with= ''is the same as= null, which is 'unknown'.
– Alex Poole
Nov 20 '18 at 17:07
add a comment |
It looks like instead of being NULL it is an empty string. SQL will regard that differently in the code.
Try:
case when networkcd = '' then row_number()....
answered Nov 19 '18 at 15:49
MapinTX
106
It looks like instead of being NULL it is an empty string. SQL will regard that differently in the code.
Try:
case when networkcd = '' then row_number()....
answered Nov 19 '18 at 15:49
MapinTX
106
answered Nov 19 '18 at 15:49
MapinTX
106
answered Nov 19 '18 at 15:49
MapinTX
106
answered Nov 19 '18 at 15:49
MapinTX
106
106
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with= ''is the same as= null, which is 'unknown'.
– Alex Poole
Nov 20 '18 at 17:07
add a comment |
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with= ''is the same as= null, which is 'unknown'.
– Alex Poole
Nov 20 '18 at 17:07
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with
= '' is the same as = null, which is 'unknown'.– Alex Poole
Nov 20 '18 at 17:07
Oracle Database treats a character value with a length of zero as null.. The result of comparing anything with
= '' is the same as = null, which is 'unknown'.– Alex Poole
Nov 20 '18 at 17:07
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%2f53377925%2fconditional-row-number-to-skip-nulls-still-counting-them%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
The row_number() is exactly that in this instance, the row number, whether you choose to display it or replace it with a null in the case statement, does not alter which row number it is.
– Andrew
Nov 19 '18 at 15:35
@Andrew That was my first thought, but doesn't the case statement for networkcd is not null adjust for that?
– sleven
Nov 19 '18 at 15:39
Afraid not, Dense_Rank as Alex has posted is the way forward, subtle difference in how it works
– Andrew
Nov 19 '18 at 15:42