SQL Select Max within a Subquery
I've been having trouble with a SQL subquery, and although I imagine this is fairly basic, the internet does not seem to hold the answer. I have a subquery inside a FROM statement which has a MAX() function within it, and I cannot seem to reference this data in the rest of the query. The query is here:
SELECT
m.nameFirst, m.nameLast, t.salary, te.name
FROM
(SELECT
MAX(salary), teamID
FROM
salaries
GROUP BY
teamID) AS t, master AS m, teams AS te, salaries AS s
WHERE
t.salary = s.salary
AND s.teamID = t.teamID
AND s.playerID = m.playerID
AND te.teamID = t.teamID;
The subquery, when run by itself, returns results which look like this:
+-------------+--------+
| MAX(salary) | teamID |
+-------------+--------+
| 13166667 | ANA |
| 16000000 | ARI |...
However, when I try to run the whole query, I get the following result:
ERROR 1054 (42S22): Unknown column 't.salary' in 'field list'
I have tried a few different things, such as t.MAX(salary), MAX(t.salary), and even just t.*, but as I need to use the subquery's results later, it just throws different errors.
What name should I use to call the results of the MAX column of the subquery?
Thanks so much for any help.
sql subquery
edited Nov 10 at 22:06
marc_s
569k12811001250
asked Nov 10 at 20:48
Noah Tatko
84
add a comment |
I've been having trouble with a SQL subquery, and although I imagine this is fairly basic, the internet does not seem to hold the answer. I have a subquery inside a FROM statement which has a MAX() function within it, and I cannot seem to reference this data in the rest of the query. The query is here:
SELECT
m.nameFirst, m.nameLast, t.salary, te.name
FROM
(SELECT
MAX(salary), teamID
FROM
salaries
GROUP BY
teamID) AS t, master AS m, teams AS te, salaries AS s
WHERE
t.salary = s.salary
AND s.teamID = t.teamID
AND s.playerID = m.playerID
AND te.teamID = t.teamID;
The subquery, when run by itself, returns results which look like this:
+-------------+--------+
| MAX(salary) | teamID |
+-------------+--------+
| 13166667 | ANA |
| 16000000 | ARI |...
However, when I try to run the whole query, I get the following result:
ERROR 1054 (42S22): Unknown column 't.salary' in 'field list'
I have tried a few different things, such as t.MAX(salary), MAX(t.salary), and even just t.*, but as I need to use the subquery's results later, it just throws different errors.
What name should I use to call the results of the MAX column of the subquery?
Thanks so much for any help.
sql subquery
edited Nov 10 at 22:06
marc_s
569k12811001250
asked Nov 10 at 20:48
Noah Tatko
84
SQL Server <> MySQL. Please which one you are really use?
– Sami
Nov 10 at 20:49
This is because there is no salary returned in subquery likewise the error name it asmax(salary) as some_namethent.some_name=s.salary
– Himanshu Ahuja
Nov 10 at 20:56
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 10 at 22:06
add a comment |
I've been having trouble with a SQL subquery, and although I imagine this is fairly basic, the internet does not seem to hold the answer. I have a subquery inside a FROM statement which has a MAX() function within it, and I cannot seem to reference this data in the rest of the query. The query is here:
SELECT
m.nameFirst, m.nameLast, t.salary, te.name
FROM
(SELECT
MAX(salary), teamID
FROM
salaries
GROUP BY
teamID) AS t, master AS m, teams AS te, salaries AS s
WHERE
t.salary = s.salary
AND s.teamID = t.teamID
AND s.playerID = m.playerID
AND te.teamID = t.teamID;
The subquery, when run by itself, returns results which look like this:
+-------------+--------+
| MAX(salary) | teamID |
+-------------+--------+
| 13166667 | ANA |
| 16000000 | ARI |...
However, when I try to run the whole query, I get the following result:
ERROR 1054 (42S22): Unknown column 't.salary' in 'field list'
I have tried a few different things, such as t.MAX(salary), MAX(t.salary), and even just t.*, but as I need to use the subquery's results later, it just throws different errors.
What name should I use to call the results of the MAX column of the subquery?
Thanks so much for any help.
sql subquery
edited Nov 10 at 22:06
marc_s
569k12811001250
asked Nov 10 at 20:48
Noah Tatko
84
I've been having trouble with a SQL subquery, and although I imagine this is fairly basic, the internet does not seem to hold the answer. I have a subquery inside a FROM statement which has a MAX() function within it, and I cannot seem to reference this data in the rest of the query. The query is here:
SELECT
m.nameFirst, m.nameLast, t.salary, te.name
FROM
(SELECT
MAX(salary), teamID
FROM
salaries
GROUP BY
teamID) AS t, master AS m, teams AS te, salaries AS s
WHERE
t.salary = s.salary
AND s.teamID = t.teamID
AND s.playerID = m.playerID
AND te.teamID = t.teamID;
The subquery, when run by itself, returns results which look like this:
+-------------+--------+
| MAX(salary) | teamID |
+-------------+--------+
| 13166667 | ANA |
| 16000000 | ARI |...
However, when I try to run the whole query, I get the following result:
ERROR 1054 (42S22): Unknown column 't.salary' in 'field list'
I have tried a few different things, such as t.MAX(salary), MAX(t.salary), and even just t.*, but as I need to use the subquery's results later, it just throws different errors.
What name should I use to call the results of the MAX column of the subquery?
Thanks so much for any help.
sql subquery
sql subquery
edited Nov 10 at 22:06
marc_s
569k12811001250
asked Nov 10 at 20:48
Noah Tatko
84
edited Nov 10 at 22:06
marc_s
569k12811001250
asked Nov 10 at 20:48
Noah Tatko
84
edited Nov 10 at 22:06
marc_s
569k12811001250
edited Nov 10 at 22:06
marc_s
569k12811001250
edited Nov 10 at 22:06
marc_s
569k12811001250
569k12811001250
asked Nov 10 at 20:48
Noah Tatko
84
asked Nov 10 at 20:48
Noah Tatko
84
asked Nov 10 at 20:48
Noah Tatko
84
84
SQL Server <> MySQL. Please which one you are really use?
– Sami
Nov 10 at 20:49
This is because there is no salary returned in subquery likewise the error name it asmax(salary) as some_namethent.some_name=s.salary
– Himanshu Ahuja
Nov 10 at 20:56
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 10 at 22:06
add a comment |
SQL Server <> MySQL. Please which one you are really use?
– Sami
Nov 10 at 20:49
This is because there is no salary returned in subquery likewise the error name it asmax(salary) as some_namethent.some_name=s.salary
– Himanshu Ahuja
Nov 10 at 20:56
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSIJOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged
– marc_s
Nov 10 at 22:06
SQL Server <> MySQL. Please which one you are really use?
– Sami
Nov 10 at 20:49
SQL Server <> MySQL. Please which one you are really use?
– Sami
Nov 10 at 20:49
This is because there is no salary returned in subquery likewise the error name it as
max(salary) as some_name then t.some_name=s.salary– Himanshu Ahuja
Nov 10 at 20:56
This is because there is no salary returned in subquery likewise the error name it as
max(salary) as some_name then t.some_name=s.salary– Himanshu Ahuja
Nov 10 at 20:56
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 10 at 22:06
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 10 at 22:06
add a comment |
3 Answers
3
active
oldest
votes
you can use an alias for max column and your code will work :
SELECT m.nameFirst, m.nameLast, t.salary, te.name
FROM (
SELECT MAX(salary) as salary, teamID
FROM salaries
GROUP BY teamID
) AS t, master AS m, teams AS te, salaries AS s
WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;
answered Nov 10 at 21:01
Mehdi akbari
544
add a comment |
you can give it an alias to make it easier or in this case accessible
eg
SELECT MAX(salary) as max_salary, teamID ...
then later simply reference
t.max_salary
so in your example change it like this
SELECT m.nameFirst, m.nameLast, t.max_salary, te.name
FROM (
SELECT MAX(salary) as max_salary, teamID
-- rest of query
answered Nov 10 at 20:51
Hogan
54.4k864102
add a comment |
This is because there is no salary returned in subquery likewise the error name it as max(salary) as some_name then t.some_name=s.salary
answered Nov 10 at 20:58
Himanshu Ahuja
1
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%2f53243265%2fsql-select-max-within-a-subquery%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
you can use an alias for max column and your code will work :
SELECT m.nameFirst, m.nameLast, t.salary, te.name
FROM (
SELECT MAX(salary) as salary, teamID
FROM salaries
GROUP BY teamID
) AS t, master AS m, teams AS te, salaries AS s
WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;
answered Nov 10 at 21:01
Mehdi akbari
544
add a comment |
you can use an alias for max column and your code will work :
SELECT m.nameFirst, m.nameLast, t.salary, te.name
FROM (
SELECT MAX(salary) as salary, teamID
FROM salaries
GROUP BY teamID
) AS t, master AS m, teams AS te, salaries AS s
WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;
answered Nov 10 at 21:01
Mehdi akbari
544
add a comment |
you can use an alias for max column and your code will work :
SELECT m.nameFirst, m.nameLast, t.salary, te.name
FROM (
SELECT MAX(salary) as salary, teamID
FROM salaries
GROUP BY teamID
) AS t, master AS m, teams AS te, salaries AS s
WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;
answered Nov 10 at 21:01
Mehdi akbari
544
you can use an alias for max column and your code will work :
SELECT m.nameFirst, m.nameLast, t.salary, te.name
FROM (
SELECT MAX(salary) as salary, teamID
FROM salaries
GROUP BY teamID
) AS t, master AS m, teams AS te, salaries AS s
WHERE t.salary=s.salary AND s.teamID=t.teamID AND s.playerID = m.playerID AND te.teamID=t.teamID;
answered Nov 10 at 21:01
Mehdi akbari
544
answered Nov 10 at 21:01
Mehdi akbari
544
answered Nov 10 at 21:01
Mehdi akbari
544
answered Nov 10 at 21:01
Mehdi akbari
544
544
add a comment |
add a comment |
you can give it an alias to make it easier or in this case accessible
eg
SELECT MAX(salary) as max_salary, teamID ...
then later simply reference
t.max_salary
so in your example change it like this
SELECT m.nameFirst, m.nameLast, t.max_salary, te.name
FROM (
SELECT MAX(salary) as max_salary, teamID
-- rest of query
answered Nov 10 at 20:51
Hogan
54.4k864102
add a comment |
you can give it an alias to make it easier or in this case accessible
eg
SELECT MAX(salary) as max_salary, teamID ...
then later simply reference
t.max_salary
so in your example change it like this
SELECT m.nameFirst, m.nameLast, t.max_salary, te.name
FROM (
SELECT MAX(salary) as max_salary, teamID
-- rest of query
answered Nov 10 at 20:51
Hogan
54.4k864102
add a comment |
you can give it an alias to make it easier or in this case accessible
eg
SELECT MAX(salary) as max_salary, teamID ...
then later simply reference
t.max_salary
so in your example change it like this
SELECT m.nameFirst, m.nameLast, t.max_salary, te.name
FROM (
SELECT MAX(salary) as max_salary, teamID
-- rest of query
answered Nov 10 at 20:51
Hogan
54.4k864102
you can give it an alias to make it easier or in this case accessible
eg
SELECT MAX(salary) as max_salary, teamID ...
then later simply reference
t.max_salary
so in your example change it like this
SELECT m.nameFirst, m.nameLast, t.max_salary, te.name
FROM (
SELECT MAX(salary) as max_salary, teamID
-- rest of query
answered Nov 10 at 20:51
Hogan
54.4k864102
answered Nov 10 at 20:51
Hogan
54.4k864102
answered Nov 10 at 20:51
Hogan
54.4k864102
answered Nov 10 at 20:51
Hogan
54.4k864102
54.4k864102
add a comment |
add a comment |
This is because there is no salary returned in subquery likewise the error name it as max(salary) as some_name then t.some_name=s.salary
answered Nov 10 at 20:58
Himanshu Ahuja
1
add a comment |
This is because there is no salary returned in subquery likewise the error name it as max(salary) as some_name then t.some_name=s.salary
answered Nov 10 at 20:58
Himanshu Ahuja
1
add a comment |
This is because there is no salary returned in subquery likewise the error name it as max(salary) as some_name then t.some_name=s.salary
answered Nov 10 at 20:58
Himanshu Ahuja
1
This is because there is no salary returned in subquery likewise the error name it as max(salary) as some_name then t.some_name=s.salary
answered Nov 10 at 20:58
Himanshu Ahuja
1
answered Nov 10 at 20:58
Himanshu Ahuja
1
answered Nov 10 at 20:58
Himanshu Ahuja
1
answered Nov 10 at 20:58
Himanshu Ahuja
1
1
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.
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%2f53243265%2fsql-select-max-within-a-subquery%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
SQL Server <> MySQL. Please which one you are really use?
– Sami
Nov 10 at 20:49
This is because there is no salary returned in subquery likewise the error name it as
max(salary) as some_namethent.some_name=s.salary– Himanshu Ahuja
Nov 10 at 20:56
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI
JOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged– marc_s
Nov 10 at 22:06