Oracle SQL order by expresion
I'm trying to sort something by an expression and for some reason it won't work unless I have that expression as a selection :
select distinct p.stuff
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
But I keep getting this error
ORA-01791: not a SELECTed expression
If I write it like this
select distinct
p.stuff,
cos(sin(to_number(p.nr_matricol)))
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
it works but I don't want to have that column printed.
Is there a way I can make this work ?
sql database oracle
edited Jan 7 at 21:16
marc_s
578k12911161262
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
add a comment |
I'm trying to sort something by an expression and for some reason it won't work unless I have that expression as a selection :
select distinct p.stuff
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
But I keep getting this error
ORA-01791: not a SELECTed expression
If I write it like this
select distinct
p.stuff,
cos(sin(to_number(p.nr_matricol)))
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
it works but I don't want to have that column printed.
Is there a way I can make this work ?
sql database oracle
edited Jan 7 at 21:16
marc_s
578k12911161262
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
The DBMS doesn't know whatnr_matricolyou are refering to, as you have aggregated rows withDISTINCT, with each original row having their ownnr_matricol. ButDISTINCTitself is often a sign for a poorly written query. Seeing you select from places only, do you need to join? Maybe anINorEXISTSclause would be better, Maybe even avoiding duplication that you must get rid of withDISTINCTthen. Something like this might work:select * from places p where p.x in (select op.y from otherplace op) order by ...).
– Thorsten Kettner
Jan 7 at 21:46
add a comment |
I'm trying to sort something by an expression and for some reason it won't work unless I have that expression as a selection :
select distinct p.stuff
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
But I keep getting this error
ORA-01791: not a SELECTed expression
If I write it like this
select distinct
p.stuff,
cos(sin(to_number(p.nr_matricol)))
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
it works but I don't want to have that column printed.
Is there a way I can make this work ?
sql database oracle
edited Jan 7 at 21:16
marc_s
578k12911161262
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
I'm trying to sort something by an expression and for some reason it won't work unless I have that expression as a selection :
select distinct p.stuff
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
But I keep getting this error
ORA-01791: not a SELECTed expression
If I write it like this
select distinct
p.stuff,
cos(sin(to_number(p.nr_matricol)))
from p.places
join otherPLACE
order by cos(sin(to_number(p.nr_matricol)));
it works but I don't want to have that column printed.
Is there a way I can make this work ?
sql database oracle
sql database oracle
edited Jan 7 at 21:16
marc_s
578k12911161262
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
edited Jan 7 at 21:16
marc_s
578k12911161262
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
edited Jan 7 at 21:16
marc_s
578k12911161262
edited Jan 7 at 21:16
marc_s
578k12911161262
edited Jan 7 at 21:16
marc_s
578k12911161262
578k12911161262
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
asked Nov 23 '18 at 23:50
Ionut EugenIonut Eugen
6213
6213
The DBMS doesn't know whatnr_matricolyou are refering to, as you have aggregated rows withDISTINCT, with each original row having their ownnr_matricol. ButDISTINCTitself is often a sign for a poorly written query. Seeing you select from places only, do you need to join? Maybe anINorEXISTSclause would be better, Maybe even avoiding duplication that you must get rid of withDISTINCTthen. Something like this might work:select * from places p where p.x in (select op.y from otherplace op) order by ...).
– Thorsten Kettner
Jan 7 at 21:46
add a comment |
The DBMS doesn't know whatnr_matricolyou are refering to, as you have aggregated rows withDISTINCT, with each original row having their ownnr_matricol. ButDISTINCTitself is often a sign for a poorly written query. Seeing you select from places only, do you need to join? Maybe anINorEXISTSclause would be better, Maybe even avoiding duplication that you must get rid of withDISTINCTthen. Something like this might work:select * from places p where p.x in (select op.y from otherplace op) order by ...).
– Thorsten Kettner
Jan 7 at 21:46
The DBMS doesn't know what
nr_matricol you are refering to, as you have aggregated rows with DISTINCT, with each original row having their own nr_matricol. But DISTINCT itself is often a sign for a poorly written query. Seeing you select from places only, do you need to join? Maybe an IN or EXISTS clause would be better, Maybe even avoiding duplication that you must get rid of with DISTINCT then. Something like this might work: select * from places p where p.x in (select op.y from otherplace op) order by ... ).– Thorsten Kettner
Jan 7 at 21:46
The DBMS doesn't know what
nr_matricol you are refering to, as you have aggregated rows with DISTINCT, with each original row having their own nr_matricol. But DISTINCT itself is often a sign for a poorly written query. Seeing you select from places only, do you need to join? Maybe an IN or EXISTS clause would be better, Maybe even avoiding duplication that you must get rid of with DISTINCT then. Something like this might work: select * from places p where p.x in (select op.y from otherplace op) order by ... ).– Thorsten Kettner
Jan 7 at 21:46
add a comment |
2 Answers
2
active
oldest
votes
The problem is that the order by takes place after the select distinct. The only values available are those in the select. A typical approach would be aggregation.
Something like this:
select p.stuff
from places p join
otherPLACE op
on . . .
group by p.stuff
order by cos(sin(to_number(max(p.nr_matricol))));
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
add a comment |
You can wrap into inline view
SELECT a FROM
(select distinct p.stuff a, cos(sin(to_number(p.nr_matricol))) b
from p.places
join otherPLACE) T
ORDER BY b;
NOTE: I hope your code was pseudo-code. Because you-re having Cartesian join, unless you specifying columns
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
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%2f53453982%2foracle-sql-order-by-expresion%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
The problem is that the order by takes place after the select distinct. The only values available are those in the select. A typical approach would be aggregation.
Something like this:
select p.stuff
from places p join
otherPLACE op
on . . .
group by p.stuff
order by cos(sin(to_number(max(p.nr_matricol))));
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
add a comment |
The problem is that the order by takes place after the select distinct. The only values available are those in the select. A typical approach would be aggregation.
Something like this:
select p.stuff
from places p join
otherPLACE op
on . . .
group by p.stuff
order by cos(sin(to_number(max(p.nr_matricol))));
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
add a comment |
The problem is that the order by takes place after the select distinct. The only values available are those in the select. A typical approach would be aggregation.
Something like this:
select p.stuff
from places p join
otherPLACE op
on . . .
group by p.stuff
order by cos(sin(to_number(max(p.nr_matricol))));
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
The problem is that the order by takes place after the select distinct. The only values available are those in the select. A typical approach would be aggregation.
Something like this:
select p.stuff
from places p join
otherPLACE op
on . . .
group by p.stuff
order by cos(sin(to_number(max(p.nr_matricol))));
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
answered Nov 24 '18 at 1:20
Gordon LinoffGordon Linoff
778k35307410
778k35307410
add a comment |
add a comment |
You can wrap into inline view
SELECT a FROM
(select distinct p.stuff a, cos(sin(to_number(p.nr_matricol))) b
from p.places
join otherPLACE) T
ORDER BY b;
NOTE: I hope your code was pseudo-code. Because you-re having Cartesian join, unless you specifying columns
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
add a comment |
You can wrap into inline view
SELECT a FROM
(select distinct p.stuff a, cos(sin(to_number(p.nr_matricol))) b
from p.places
join otherPLACE) T
ORDER BY b;
NOTE: I hope your code was pseudo-code. Because you-re having Cartesian join, unless you specifying columns
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
add a comment |
You can wrap into inline view
SELECT a FROM
(select distinct p.stuff a, cos(sin(to_number(p.nr_matricol))) b
from p.places
join otherPLACE) T
ORDER BY b;
NOTE: I hope your code was pseudo-code. Because you-re having Cartesian join, unless you specifying columns
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
You can wrap into inline view
SELECT a FROM
(select distinct p.stuff a, cos(sin(to_number(p.nr_matricol))) b
from p.places
join otherPLACE) T
ORDER BY b;
NOTE: I hope your code was pseudo-code. Because you-re having Cartesian join, unless you specifying columns
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
answered Nov 23 '18 at 23:55
T.S.T.S.
9,953103353
9,953103353
add a comment |
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.
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%2f53453982%2foracle-sql-order-by-expresion%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 DBMS doesn't know what
nr_matricolyou are refering to, as you have aggregated rows withDISTINCT, with each original row having their ownnr_matricol. ButDISTINCTitself is often a sign for a poorly written query. Seeing you select from places only, do you need to join? Maybe anINorEXISTSclause would be better, Maybe even avoiding duplication that you must get rid of withDISTINCTthen. Something like this might work:select * from places p where p.x in (select op.y from otherplace op) order by ...).– Thorsten Kettner
Jan 7 at 21:46