How to use Oracle CTE for such query?
I have a query that looks like following:
SELECT m.Name, (m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
ORDER BY 1;
These query produces multiple rows for each Name.
I need to limit it to one row for each Name with the following logic: include the row which Value is the closest to the average Value for a given Name.
Something tells me that CTE should allow for a more compact code and hopefully more effective implementation, so I do not need to repeat the virtually same query several times.
Can you please point me into the right direction?
sql oracle11g common-table-expression
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
add a comment |
I have a query that looks like following:
SELECT m.Name, (m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
ORDER BY 1;
These query produces multiple rows for each Name.
I need to limit it to one row for each Name with the following logic: include the row which Value is the closest to the average Value for a given Name.
Something tells me that CTE should allow for a more compact code and hopefully more effective implementation, so I do not need to repeat the virtually same query several times.
Can you please point me into the right direction?
sql oracle11g common-table-expression
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
add a comment |
I have a query that looks like following:
SELECT m.Name, (m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
ORDER BY 1;
These query produces multiple rows for each Name.
I need to limit it to one row for each Name with the following logic: include the row which Value is the closest to the average Value for a given Name.
Something tells me that CTE should allow for a more compact code and hopefully more effective implementation, so I do not need to repeat the virtually same query several times.
Can you please point me into the right direction?
sql oracle11g common-table-expression
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
I have a query that looks like following:
SELECT m.Name, (m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
ORDER BY 1;
These query produces multiple rows for each Name.
I need to limit it to one row for each Name with the following logic: include the row which Value is the closest to the average Value for a given Name.
Something tells me that CTE should allow for a more compact code and hopefully more effective implementation, so I do not need to repeat the virtually same query several times.
Can you please point me into the right direction?
sql oracle11g common-table-expression
sql oracle11g common-table-expression
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
asked Mar 6 '13 at 1:45
PM 77-1PM 77-1
8,847144585
8,847144585
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
A CTE can help, but I think the key are the analytic functions. First, you can calculate the average of value for each name using an analytic function. Then you can rank the absolute value of the differences.
Here is the version with the CTE:
with t as (
SELECT m.Name,
(m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
)
select t.*
from (select t.*,
row_number() over (partition by name order by avgdiff) as seqnum
from (select t.*,
abs(value - avg(value) over (partition by name)) as AvgDiff
from t
) t
) t
where seqnum = 1
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
5
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
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%2f15237505%2fhow-to-use-oracle-cte-for-such-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
A CTE can help, but I think the key are the analytic functions. First, you can calculate the average of value for each name using an analytic function. Then you can rank the absolute value of the differences.
Here is the version with the CTE:
with t as (
SELECT m.Name,
(m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
)
select t.*
from (select t.*,
row_number() over (partition by name order by avgdiff) as seqnum
from (select t.*,
abs(value - avg(value) over (partition by name)) as AvgDiff
from t
) t
) t
where seqnum = 1
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
5
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
add a comment |
A CTE can help, but I think the key are the analytic functions. First, you can calculate the average of value for each name using an analytic function. Then you can rank the absolute value of the differences.
Here is the version with the CTE:
with t as (
SELECT m.Name,
(m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
)
select t.*
from (select t.*,
row_number() over (partition by name order by avgdiff) as seqnum
from (select t.*,
abs(value - avg(value) over (partition by name)) as AvgDiff
from t
) t
) t
where seqnum = 1
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
5
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
add a comment |
A CTE can help, but I think the key are the analytic functions. First, you can calculate the average of value for each name using an analytic function. Then you can rank the absolute value of the differences.
Here is the version with the CTE:
with t as (
SELECT m.Name,
(m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
)
select t.*
from (select t.*,
row_number() over (partition by name order by avgdiff) as seqnum
from (select t.*,
abs(value - avg(value) over (partition by name)) as AvgDiff
from t
) t
) t
where seqnum = 1
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
A CTE can help, but I think the key are the analytic functions. First, you can calculate the average of value for each name using an analytic function. Then you can rank the absolute value of the differences.
Here is the version with the CTE:
with t as (
SELECT m.Name,
(m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
FROM m MainTable
LEFT JOIN Additional1 a1 ON (...)
LEFT JOIN Additional2 a2 ON (...)
LEFT JOIN Additional3 a3 ON (...)
WHERE (conditions on m)
)
select t.*
from (select t.*,
row_number() over (partition by name order by avgdiff) as seqnum
from (select t.*,
abs(value - avg(value) over (partition by name)) as AvgDiff
from t
) t
) t
where seqnum = 1
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
answered Mar 6 '13 at 1:58
Gordon LinoffGordon Linoff
766k35300402
766k35300402
5
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
add a comment |
5
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
5
5
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
I'm a bit confused here. Why is the "outside" query and both internal queries all have the same alias?
– PM 77-1
Mar 6 '13 at 2:09
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%2f15237505%2fhow-to-use-oracle-cte-for-such-query%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
