Using Subquery select to get column value
0
I have the following query:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
I get the error, "Previous_date not on column list" when trying to call the function f_prevundadjprice. Basically what I want to do is create the column previous date using (SELECT MAX..) and then use the value from this column in the function f_prevunadjprice.
mysql sql
asked Nov 15 '18 at 16:24
ChrisChris
1089
add a comment |
0
I have the following query:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
I get the error, "Previous_date not on column list" when trying to call the function f_prevundadjprice. Basically what I want to do is create the column previous date using (SELECT MAX..) and then use the value from this column in the function f_prevunadjprice.
mysql sql
asked Nov 15 '18 at 16:24
ChrisChris
1089
1
Use a subquery. You can't use an alias in the sameselect.
– Gordon Linoff
Nov 15 '18 at 16:26
@GordonLinoff How would you rewrite the query? I tried but I could not get it to work. Also, why is the same SELECT? I have the (SELECT MAX..) in parentheses, so does this not get computed first? is this not already a subquery?
– Chris
Nov 15 '18 at 16:27
Posting a broken query without telling us what you are trying to do will not help us help you. Tell us what you try to do so that we can get a better understanding. Read this. stackoverflow.com/help/how-to-ask
– Eric
Nov 15 '18 at 17:17
@Eric I tried to correct it.
– Chris
Nov 15 '18 at 17:26
add a comment |
0
0
0
I have the following query:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
I get the error, "Previous_date not on column list" when trying to call the function f_prevundadjprice. Basically what I want to do is create the column previous date using (SELECT MAX..) and then use the value from this column in the function f_prevunadjprice.
mysql sql
asked Nov 15 '18 at 16:24
ChrisChris
1089
I have the following query:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
I get the error, "Previous_date not on column list" when trying to call the function f_prevundadjprice. Basically what I want to do is create the column previous date using (SELECT MAX..) and then use the value from this column in the function f_prevunadjprice.
mysql sql
mysql sql
asked Nov 15 '18 at 16:24
ChrisChris
1089
asked Nov 15 '18 at 16:24
ChrisChris
1089
edited Nov 15 '18 at 17:22
Chris
asked Nov 15 '18 at 16:24
ChrisChris
1089
asked Nov 15 '18 at 16:24
ChrisChris
1089
asked Nov 15 '18 at 16:24
ChrisChris
1089
1089
1
Use a subquery. You can't use an alias in the sameselect.
– Gordon Linoff
Nov 15 '18 at 16:26
@GordonLinoff How would you rewrite the query? I tried but I could not get it to work. Also, why is the same SELECT? I have the (SELECT MAX..) in parentheses, so does this not get computed first? is this not already a subquery?
– Chris
Nov 15 '18 at 16:27
Posting a broken query without telling us what you are trying to do will not help us help you. Tell us what you try to do so that we can get a better understanding. Read this. stackoverflow.com/help/how-to-ask
– Eric
Nov 15 '18 at 17:17
@Eric I tried to correct it.
– Chris
Nov 15 '18 at 17:26
add a comment |
1
Use a subquery. You can't use an alias in the sameselect.
– Gordon Linoff
Nov 15 '18 at 16:26
@GordonLinoff How would you rewrite the query? I tried but I could not get it to work. Also, why is the same SELECT? I have the (SELECT MAX..) in parentheses, so does this not get computed first? is this not already a subquery?
– Chris
Nov 15 '18 at 16:27
Posting a broken query without telling us what you are trying to do will not help us help you. Tell us what you try to do so that we can get a better understanding. Read this. stackoverflow.com/help/how-to-ask
– Eric
Nov 15 '18 at 17:17
@Eric I tried to correct it.
– Chris
Nov 15 '18 at 17:26
1
1
Use a subquery. You can't use an alias in the same
select.– Gordon Linoff
Nov 15 '18 at 16:26
Use a subquery. You can't use an alias in the same
select.– Gordon Linoff
Nov 15 '18 at 16:26
@GordonLinoff How would you rewrite the query? I tried but I could not get it to work. Also, why is the same SELECT? I have the (SELECT MAX..) in parentheses, so does this not get computed first? is this not already a subquery?
– Chris
Nov 15 '18 at 16:27
@GordonLinoff How would you rewrite the query? I tried but I could not get it to work. Also, why is the same SELECT? I have the (SELECT MAX..) in parentheses, so does this not get computed first? is this not already a subquery?
– Chris
Nov 15 '18 at 16:27
Posting a broken query without telling us what you are trying to do will not help us help you. Tell us what you try to do so that we can get a better understanding. Read this. stackoverflow.com/help/how-to-ask
– Eric
Nov 15 '18 at 17:17
Posting a broken query without telling us what you are trying to do will not help us help you. Tell us what you try to do so that we can get a better understanding. Read this. stackoverflow.com/help/how-to-ask
– Eric
Nov 15 '18 at 17:17
@Eric I tried to correct it.
– Chris
Nov 15 '18 at 17:26
@Eric I tried to correct it.
– Chris
Nov 15 '18 at 17:26
add a comment |
2 Answers
2
active
oldest
votes
0
If you use p. to reference all the fields coming from fp_v2_fp_basic_prices , you should also do it in the next line:
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price)
try to change it by:
f_prevunadjprice(p.fsym_id,p.p_date,p.Previous_Date,p.p_price)
If you continue with the same error, you should ensure the table/view fp_v2_fp_basic_prices has a column named Previous_Date
You should also use AS here:
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
So:
...) AS Previous_Date
answered Nov 15 '18 at 16:53
JortxJortx
3411215
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
add a comment |
0
you already have one subquery. If you were using TSQL you could outer apply the second one. MySQL doesn't support apply, so you'll have to left join to it:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
PreviousDate.maxdate
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
LEFT JOIN (
SELECT f.fsym_id,f.p_date,MAX(f.p_date) as maxdate
FROM fp_v2_fp_basic_prices AS f
group by f.fsym_id, f.p_date
) Previous_Date
on Previous_Date.fsym_id = p.fsym_id and AND Previous_Date.p_date<p.p_date
answered Nov 15 '18 at 16:48
iainciainc
283212
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
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
});
}
});
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%2f53323798%2fusing-subquery-select-to-get-column-value%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
0
If you use p. to reference all the fields coming from fp_v2_fp_basic_prices , you should also do it in the next line:
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price)
try to change it by:
f_prevunadjprice(p.fsym_id,p.p_date,p.Previous_Date,p.p_price)
If you continue with the same error, you should ensure the table/view fp_v2_fp_basic_prices has a column named Previous_Date
You should also use AS here:
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
So:
...) AS Previous_Date
answered Nov 15 '18 at 16:53
JortxJortx
3411215
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
add a comment |
0
If you use p. to reference all the fields coming from fp_v2_fp_basic_prices , you should also do it in the next line:
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price)
try to change it by:
f_prevunadjprice(p.fsym_id,p.p_date,p.Previous_Date,p.p_price)
If you continue with the same error, you should ensure the table/view fp_v2_fp_basic_prices has a column named Previous_Date
You should also use AS here:
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
So:
...) AS Previous_Date
answered Nov 15 '18 at 16:53
JortxJortx
3411215
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
add a comment |
0
0
0
If you use p. to reference all the fields coming from fp_v2_fp_basic_prices , you should also do it in the next line:
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price)
try to change it by:
f_prevunadjprice(p.fsym_id,p.p_date,p.Previous_Date,p.p_price)
If you continue with the same error, you should ensure the table/view fp_v2_fp_basic_prices has a column named Previous_Date
You should also use AS here:
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
So:
...) AS Previous_Date
answered Nov 15 '18 at 16:53
JortxJortx
3411215
If you use p. to reference all the fields coming from fp_v2_fp_basic_prices , you should also do it in the next line:
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price)
try to change it by:
f_prevunadjprice(p.fsym_id,p.p_date,p.Previous_Date,p.p_price)
If you continue with the same error, you should ensure the table/view fp_v2_fp_basic_prices has a column named Previous_Date
You should also use AS here:
(
SELECT MAX(f.p_date)
FROM fp_v2_fp_basic_prices AS f
WHERE f.fsym_id = p.fsym_id AND f.p_date<p.p_date
) Previous_Date
So:
...) AS Previous_Date
answered Nov 15 '18 at 16:53
JortxJortx
3411215
answered Nov 15 '18 at 16:53
JortxJortx
3411215
answered Nov 15 '18 at 16:53
JortxJortx
3411215
answered Nov 15 '18 at 16:53
JortxJortx
3411215
3411215
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
add a comment |
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
The table fp_v2.. does not have a column named Previous_Date. Please check my updated question :)
– Chris
Nov 15 '18 at 17:50
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
In that case the error is right, there's not any column called Previous_Date in column list. What you can do is to pass to the function the value of Prevuious_Date (SELECT MAX(f.p_date)...). Another way is to pass only f.p_date to the function, and calculate the Previous_Date inside the function...
– Jortx
Nov 16 '18 at 8:57
add a comment |
0
you already have one subquery. If you were using TSQL you could outer apply the second one. MySQL doesn't support apply, so you'll have to left join to it:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
PreviousDate.maxdate
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
LEFT JOIN (
SELECT f.fsym_id,f.p_date,MAX(f.p_date) as maxdate
FROM fp_v2_fp_basic_prices AS f
group by f.fsym_id, f.p_date
) Previous_Date
on Previous_Date.fsym_id = p.fsym_id and AND Previous_Date.p_date<p.p_date
answered Nov 15 '18 at 16:48
iainciainc
283212
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
add a comment |
0
you already have one subquery. If you were using TSQL you could outer apply the second one. MySQL doesn't support apply, so you'll have to left join to it:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
PreviousDate.maxdate
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
LEFT JOIN (
SELECT f.fsym_id,f.p_date,MAX(f.p_date) as maxdate
FROM fp_v2_fp_basic_prices AS f
group by f.fsym_id, f.p_date
) Previous_Date
on Previous_Date.fsym_id = p.fsym_id and AND Previous_Date.p_date<p.p_date
answered Nov 15 '18 at 16:48
iainciainc
283212
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
add a comment |
0
0
0
you already have one subquery. If you were using TSQL you could outer apply the second one. MySQL doesn't support apply, so you'll have to left join to it:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
PreviousDate.maxdate
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
LEFT JOIN (
SELECT f.fsym_id,f.p_date,MAX(f.p_date) as maxdate
FROM fp_v2_fp_basic_prices AS f
group by f.fsym_id, f.p_date
) Previous_Date
on Previous_Date.fsym_id = p.fsym_id and AND Previous_Date.p_date<p.p_date
answered Nov 15 '18 at 16:48
iainciainc
283212
you already have one subquery. If you were using TSQL you could outer apply the second one. MySQL doesn't support apply, so you'll have to left join to it:
SELECT
p.fsym_id,
b.p_co_sec_name_desc AS Company_Name,
p.p_date,
p.p_price AS Unadjusted_Price,
b.region AS Region,
f_splitadjprice(p.fsym_id,p.p_date,p.p_price) AS O_Split_Adjusted_Price,
f_prevunadjprice(p.fsym_id,p.p_date,Previous_Date,p.p_price),
PreviousDate.maxdate
FROM
fp_v2_fp_basic_prices p
LEFT JOIN (
SELECT r2.region, b2.p_co_sec_name_desc, b2.fsym_id
FROM fp_v2_fp_sec_coverage b2
LEFT JOIN sym_v1_sym_region r2 ON b2.fsym_id = r2.fsym_id
WHERE r2.region = "EUR") b
ON b.fsym_id =p.fsym_id;
LEFT JOIN (
SELECT f.fsym_id,f.p_date,MAX(f.p_date) as maxdate
FROM fp_v2_fp_basic_prices AS f
group by f.fsym_id, f.p_date
) Previous_Date
on Previous_Date.fsym_id = p.fsym_id and AND Previous_Date.p_date<p.p_date
answered Nov 15 '18 at 16:48
iainciainc
283212
edited Nov 16 '18 at 16:03
answered Nov 15 '18 at 16:48
iainciainc
283212
answered Nov 15 '18 at 16:48
iainciainc
283212
answered Nov 15 '18 at 16:48
iainciainc
283212
283212
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
add a comment |
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
How is SQL reading this query? What is being done first? The use of parentheses also looks weird to me. There is a missing ).
– Chris
Nov 15 '18 at 16:53
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
HI Chris, amended the missing bracket. So SQL gets all the data from fp_basic_prices. It joins to the results of the first subquery as a table, aliased as b.. Then joins to the results of the second subquery which gets all of the maxdates grouped by fsym_id, but is joined to by that Id so only one value is returned to the end result.
– iainc
Nov 16 '18 at 16:04
add a comment |
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%2f53323798%2fusing-subquery-select-to-get-column-value%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
Use a subquery. You can't use an alias in the same
select.– Gordon Linoff
Nov 15 '18 at 16:26
@GordonLinoff How would you rewrite the query? I tried but I could not get it to work. Also, why is the same SELECT? I have the (SELECT MAX..) in parentheses, so does this not get computed first? is this not already a subquery?
– Chris
Nov 15 '18 at 16:27
Posting a broken query without telling us what you are trying to do will not help us help you. Tell us what you try to do so that we can get a better understanding. Read this. stackoverflow.com/help/how-to-ask
– Eric
Nov 15 '18 at 17:17
@Eric I tried to correct it.
– Chris
Nov 15 '18 at 17:26