Dense_rank order by on more than 999 characters
4
We just found a weird behavior on Oracle Database
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 998, 'a') || '0' val from dual
union all
select lpad('a', 998, 'a') || '1' val from dual
);
Will return as expected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
2 |aaaa[...]aaaaa1
But then we add 1 character
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
And the result is unexpected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
1 |aaaa[...]aaaaa1
So I have to write :
select dense_rank() over (order by val, substr(val, 999, 999)) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
Can someone explain why this happen ? How to prevent it ? thanks
sql oracle oracle11g window-functions
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
asked Oct 11 '18 at 14:25
CyrilCyril
211
add a comment |
4
We just found a weird behavior on Oracle Database
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 998, 'a') || '0' val from dual
union all
select lpad('a', 998, 'a') || '1' val from dual
);
Will return as expected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
2 |aaaa[...]aaaaa1
But then we add 1 character
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
And the result is unexpected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
1 |aaaa[...]aaaaa1
So I have to write :
select dense_rank() over (order by val, substr(val, 999, 999)) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
Can someone explain why this happen ? How to prevent it ? thanks
sql oracle oracle11g window-functions
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
asked Oct 11 '18 at 14:25
CyrilCyril
211
1
I don't see this behaviour in Oracle11g as you mentioned. See here: dbfiddle.uk/…
– Kaushik Nayak
Oct 11 '18 at 14:48
1
Works fine for me on11.2.0.4.0
– Marmite Bomber
Oct 11 '18 at 15:00
1
I don't see this behavior on Oracle 12.2.0.1 either. What is your Oracle version? And what is your interface to the DB (SQL Developer? Toad? Simple SQL*Plus? Something else?)
– mathguy
Oct 11 '18 at 15:07
1
I confirm, wrong results in versions 10.2.0.4 and 11.2.0.1. And here I see something about bug 12647744, DENSE_RANK returns wrong results corrected in 11.2.0.4. Don't know more details.
– Ponder Stibbons
Oct 11 '18 at 15:28
add a comment |
4
4
4
We just found a weird behavior on Oracle Database
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 998, 'a') || '0' val from dual
union all
select lpad('a', 998, 'a') || '1' val from dual
);
Will return as expected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
2 |aaaa[...]aaaaa1
But then we add 1 character
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
And the result is unexpected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
1 |aaaa[...]aaaaa1
So I have to write :
select dense_rank() over (order by val, substr(val, 999, 999)) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
Can someone explain why this happen ? How to prevent it ? thanks
sql oracle oracle11g window-functions
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
asked Oct 11 '18 at 14:25
CyrilCyril
211
We just found a weird behavior on Oracle Database
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 998, 'a') || '0' val from dual
union all
select lpad('a', 998, 'a') || '1' val from dual
);
Will return as expected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
2 |aaaa[...]aaaaa1
But then we add 1 character
select dense_rank() over (order by val) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
And the result is unexpected :
RANK |VAL
-----|-----------------
1 |aaaa[...]aaaaa0
1 |aaaa[...]aaaaa1
So I have to write :
select dense_rank() over (order by val, substr(val, 999, 999)) rank, val
from (
select lpad('a', 999, 'a') || '0' val from dual
union all
select lpad('a', 999, 'a') || '1' val from dual
);
Can someone explain why this happen ? How to prevent it ? thanks
sql oracle oracle11g window-functions
sql oracle oracle11g window-functions
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
asked Oct 11 '18 at 14:25
CyrilCyril
211
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
asked Oct 11 '18 at 14:25
CyrilCyril
211
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
edited Oct 31 '18 at 9:19
a_horse_with_no_name
300k46458551
300k46458551
asked Oct 11 '18 at 14:25
CyrilCyril
211
asked Oct 11 '18 at 14:25
CyrilCyril
211
asked Oct 11 '18 at 14:25
CyrilCyril
211
211
1
I don't see this behaviour in Oracle11g as you mentioned. See here: dbfiddle.uk/…
– Kaushik Nayak
Oct 11 '18 at 14:48
1
Works fine for me on11.2.0.4.0
– Marmite Bomber
Oct 11 '18 at 15:00
1
I don't see this behavior on Oracle 12.2.0.1 either. What is your Oracle version? And what is your interface to the DB (SQL Developer? Toad? Simple SQL*Plus? Something else?)
– mathguy
Oct 11 '18 at 15:07
1
I confirm, wrong results in versions 10.2.0.4 and 11.2.0.1. And here I see something about bug 12647744, DENSE_RANK returns wrong results corrected in 11.2.0.4. Don't know more details.
– Ponder Stibbons
Oct 11 '18 at 15:28
add a comment |
1
I don't see this behaviour in Oracle11g as you mentioned. See here: dbfiddle.uk/…
– Kaushik Nayak
Oct 11 '18 at 14:48
1
Works fine for me on11.2.0.4.0
– Marmite Bomber
Oct 11 '18 at 15:00
1
I don't see this behavior on Oracle 12.2.0.1 either. What is your Oracle version? And what is your interface to the DB (SQL Developer? Toad? Simple SQL*Plus? Something else?)
– mathguy
Oct 11 '18 at 15:07
1
I confirm, wrong results in versions 10.2.0.4 and 11.2.0.1. And here I see something about bug 12647744, DENSE_RANK returns wrong results corrected in 11.2.0.4. Don't know more details.
– Ponder Stibbons
Oct 11 '18 at 15:28
1
1
I don't see this behaviour in Oracle11g as you mentioned. See here: dbfiddle.uk/…
– Kaushik Nayak
Oct 11 '18 at 14:48
I don't see this behaviour in Oracle11g as you mentioned. See here: dbfiddle.uk/…
– Kaushik Nayak
Oct 11 '18 at 14:48
1
1
Works fine for me on
11.2.0.4.0– Marmite Bomber
Oct 11 '18 at 15:00
Works fine for me on
11.2.0.4.0– Marmite Bomber
Oct 11 '18 at 15:00
1
1
I don't see this behavior on Oracle 12.2.0.1 either. What is your Oracle version? And what is your interface to the DB (SQL Developer? Toad? Simple SQL*Plus? Something else?)
– mathguy
Oct 11 '18 at 15:07
I don't see this behavior on Oracle 12.2.0.1 either. What is your Oracle version? And what is your interface to the DB (SQL Developer? Toad? Simple SQL*Plus? Something else?)
– mathguy
Oct 11 '18 at 15:07
1
1
I confirm, wrong results in versions 10.2.0.4 and 11.2.0.1. And here I see something about bug 12647744, DENSE_RANK returns wrong results corrected in 11.2.0.4. Don't know more details.
– Ponder Stibbons
Oct 11 '18 at 15:28
I confirm, wrong results in versions 10.2.0.4 and 11.2.0.1. And here I see something about bug 12647744, DENSE_RANK returns wrong results corrected in 11.2.0.4. Don't know more details.
– Ponder Stibbons
Oct 11 '18 at 15:28
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f52762567%2fdense-rank-order-by-on-more-than-999-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f52762567%2fdense-rank-order-by-on-more-than-999-characters%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
1
I don't see this behaviour in Oracle11g as you mentioned. See here: dbfiddle.uk/…
– Kaushik Nayak
Oct 11 '18 at 14:48
1
Works fine for me on
11.2.0.4.0– Marmite Bomber
Oct 11 '18 at 15:00
1
I don't see this behavior on Oracle 12.2.0.1 either. What is your Oracle version? And what is your interface to the DB (SQL Developer? Toad? Simple SQL*Plus? Something else?)
– mathguy
Oct 11 '18 at 15:07
1
I confirm, wrong results in versions 10.2.0.4 and 11.2.0.1. And here I see something about bug 12647744, DENSE_RANK returns wrong results corrected in 11.2.0.4. Don't know more details.
– Ponder Stibbons
Oct 11 '18 at 15:28