Oracle SQL join query to find highest salary
So I have two tables salary and emp whose definition is shown as below
[
I am tring to create a query that Find the employee who draws the maximum salary and Display the employee details along with the nationality.
I created this query
select empcode,
max(basic) as "Highest Sal"
from salary
join emp on empcode;
Please help with this
oracle join top-n
edited Nov 27 '18 at 10:23
APC
120k15119230
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
add a comment |
So I have two tables salary and emp whose definition is shown as below
[
I am tring to create a query that Find the employee who draws the maximum salary and Display the employee details along with the nationality.
I created this query
select empcode,
max(basic) as "Highest Sal"
from salary
join emp on empcode;
Please help with this
oracle join top-n
edited Nov 27 '18 at 10:23
APC
120k15119230
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
add a comment |
So I have two tables salary and emp whose definition is shown as below
[
I am tring to create a query that Find the employee who draws the maximum salary and Display the employee details along with the nationality.
I created this query
select empcode,
max(basic) as "Highest Sal"
from salary
join emp on empcode;
Please help with this
oracle join top-n
edited Nov 27 '18 at 10:23
APC
120k15119230
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
So I have two tables salary and emp whose definition is shown as below
[
I am tring to create a query that Find the employee who draws the maximum salary and Display the employee details along with the nationality.
I created this query
select empcode,
max(basic) as "Highest Sal"
from salary
join emp on empcode;
Please help with this
oracle join top-n
oracle join top-n
edited Nov 27 '18 at 10:23
APC
120k15119230
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
edited Nov 27 '18 at 10:23
APC
120k15119230
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
edited Nov 27 '18 at 10:23
APC
120k15119230
edited Nov 27 '18 at 10:23
APC
120k15119230
edited Nov 27 '18 at 10:23
APC
120k15119230
120k15119230
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
asked Nov 25 '18 at 18:09
MrLazyStudentMrLazyStudent
82
82
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Your query uses a simple aggregate max(basic) which would find the highest salary. Except you need to join to the EMP table to display other details. This means you can't use aggregation, because we need to GROUP BY the non-aggregated columns, which would make a nonsense of the query.
Fortunately we can solve the problem with an analytic function. The subquery selects all the relevant information and ranks each employee by salary, with a rank of 1 being the highest paid. We use rank() here because that will handle ties: two employees with the same basic will be in the same rank.
select empcode
, empname
, nationality
, "Highest Sal"
from (
select emp.empcode
, emp.empname
, emp.nationality
, salary.basic as "Highest Sal"
, rank() over (order by salary.basic desc ) as rnk
from salary join emp on emp.empcode = salary.empcode
)
where rnk = 1;
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
add a comment |
Find the employee who draws the maximum salary
An employee can have multiple salaries in your datamodel. An employee's (total) salary hence is the sum of these. You want to find the maximum salary per employee and show the employee(s) earning that much.
You can use MAX OVER to find the maximum sum:
select e.*, s.total_salary
from emp e
join
(
select
empcode,
sum(basic) as total_salary,
max(sum(basic)) over () as max_total_salary
from salary
) s on s.empcode = e.empcode and s.total_salary = s.max_total_salary
order by e.empcode;
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
add a comment |
Try this:
SELECT * FROM
(SELECT E.EmpCode, E.EmpName, E.DOB, E.DOJ, E.DeptCode, E.DesgCode, E.PhNo,
E.Qualification, E.Nationality, S.Basic, S.HRA, S.TA, S.UTA, S.OTRate
FROM EMP AS E JOIN SALARY AS S ON (E.EmpCode = S.EmpCode) order by S.Basic desc)
WHERE rownum = 1
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
==is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highestbasicsalary.
– APC
Nov 25 '18 at 20:22
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
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%2f53470404%2foracle-sql-join-query-to-find-highest-salary%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
Your query uses a simple aggregate max(basic) which would find the highest salary. Except you need to join to the EMP table to display other details. This means you can't use aggregation, because we need to GROUP BY the non-aggregated columns, which would make a nonsense of the query.
Fortunately we can solve the problem with an analytic function. The subquery selects all the relevant information and ranks each employee by salary, with a rank of 1 being the highest paid. We use rank() here because that will handle ties: two employees with the same basic will be in the same rank.
select empcode
, empname
, nationality
, "Highest Sal"
from (
select emp.empcode
, emp.empname
, emp.nationality
, salary.basic as "Highest Sal"
, rank() over (order by salary.basic desc ) as rnk
from salary join emp on emp.empcode = salary.empcode
)
where rnk = 1;
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
add a comment |
Your query uses a simple aggregate max(basic) which would find the highest salary. Except you need to join to the EMP table to display other details. This means you can't use aggregation, because we need to GROUP BY the non-aggregated columns, which would make a nonsense of the query.
Fortunately we can solve the problem with an analytic function. The subquery selects all the relevant information and ranks each employee by salary, with a rank of 1 being the highest paid. We use rank() here because that will handle ties: two employees with the same basic will be in the same rank.
select empcode
, empname
, nationality
, "Highest Sal"
from (
select emp.empcode
, emp.empname
, emp.nationality
, salary.basic as "Highest Sal"
, rank() over (order by salary.basic desc ) as rnk
from salary join emp on emp.empcode = salary.empcode
)
where rnk = 1;
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
add a comment |
Your query uses a simple aggregate max(basic) which would find the highest salary. Except you need to join to the EMP table to display other details. This means you can't use aggregation, because we need to GROUP BY the non-aggregated columns, which would make a nonsense of the query.
Fortunately we can solve the problem with an analytic function. The subquery selects all the relevant information and ranks each employee by salary, with a rank of 1 being the highest paid. We use rank() here because that will handle ties: two employees with the same basic will be in the same rank.
select empcode
, empname
, nationality
, "Highest Sal"
from (
select emp.empcode
, emp.empname
, emp.nationality
, salary.basic as "Highest Sal"
, rank() over (order by salary.basic desc ) as rnk
from salary join emp on emp.empcode = salary.empcode
)
where rnk = 1;
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
Your query uses a simple aggregate max(basic) which would find the highest salary. Except you need to join to the EMP table to display other details. This means you can't use aggregation, because we need to GROUP BY the non-aggregated columns, which would make a nonsense of the query.
Fortunately we can solve the problem with an analytic function. The subquery selects all the relevant information and ranks each employee by salary, with a rank of 1 being the highest paid. We use rank() here because that will handle ties: two employees with the same basic will be in the same rank.
select empcode
, empname
, nationality
, "Highest Sal"
from (
select emp.empcode
, emp.empname
, emp.nationality
, salary.basic as "Highest Sal"
, rank() over (order by salary.basic desc ) as rnk
from salary join emp on emp.empcode = salary.empcode
)
where rnk = 1;
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
edited Nov 26 '18 at 7:56
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
answered Nov 25 '18 at 18:22
APCAPC
120k15119230
120k15119230
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
add a comment |
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Perfect answer. Bravo. Well done. :-)
– Bob Jarvis
Nov 25 '18 at 19:36
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
Looks good though I have tried it and it gives an error at line 12: ORA-00920: INVALID RELATIONAL OPERATOR
– MrLazyStudent
Nov 26 '18 at 4:07
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
You are a lifesaver @APC . It worked like a charm
– MrLazyStudent
Nov 26 '18 at 17:46
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
@mrlazystudent - if you found this answer helpful please accept and/or upvote it. Accepted answers improve the quality of this site as a resource for future Seekers.
– APC
Nov 27 '18 at 10:18
add a comment |
Find the employee who draws the maximum salary
An employee can have multiple salaries in your datamodel. An employee's (total) salary hence is the sum of these. You want to find the maximum salary per employee and show the employee(s) earning that much.
You can use MAX OVER to find the maximum sum:
select e.*, s.total_salary
from emp e
join
(
select
empcode,
sum(basic) as total_salary,
max(sum(basic)) over () as max_total_salary
from salary
) s on s.empcode = e.empcode and s.total_salary = s.max_total_salary
order by e.empcode;
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
add a comment |
Find the employee who draws the maximum salary
An employee can have multiple salaries in your datamodel. An employee's (total) salary hence is the sum of these. You want to find the maximum salary per employee and show the employee(s) earning that much.
You can use MAX OVER to find the maximum sum:
select e.*, s.total_salary
from emp e
join
(
select
empcode,
sum(basic) as total_salary,
max(sum(basic)) over () as max_total_salary
from salary
) s on s.empcode = e.empcode and s.total_salary = s.max_total_salary
order by e.empcode;
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
add a comment |
Find the employee who draws the maximum salary
An employee can have multiple salaries in your datamodel. An employee's (total) salary hence is the sum of these. You want to find the maximum salary per employee and show the employee(s) earning that much.
You can use MAX OVER to find the maximum sum:
select e.*, s.total_salary
from emp e
join
(
select
empcode,
sum(basic) as total_salary,
max(sum(basic)) over () as max_total_salary
from salary
) s on s.empcode = e.empcode and s.total_salary = s.max_total_salary
order by e.empcode;
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
Find the employee who draws the maximum salary
An employee can have multiple salaries in your datamodel. An employee's (total) salary hence is the sum of these. You want to find the maximum salary per employee and show the employee(s) earning that much.
You can use MAX OVER to find the maximum sum:
select e.*, s.total_salary
from emp e
join
(
select
empcode,
sum(basic) as total_salary,
max(sum(basic)) over () as max_total_salary
from salary
) s on s.empcode = e.empcode and s.total_salary = s.max_total_salary
order by e.empcode;
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
answered Nov 26 '18 at 9:39
Thorsten KettnerThorsten Kettner
52.5k32643
52.5k32643
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
add a comment |
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
Keen piece of observation. I suspect the 1:M relationship is a flaw in the data model rather than an actual business rule.
– APC
Nov 27 '18 at 10:22
add a comment |
Try this:
SELECT * FROM
(SELECT E.EmpCode, E.EmpName, E.DOB, E.DOJ, E.DeptCode, E.DesgCode, E.PhNo,
E.Qualification, E.Nationality, S.Basic, S.HRA, S.TA, S.UTA, S.OTRate
FROM EMP AS E JOIN SALARY AS S ON (E.EmpCode = S.EmpCode) order by S.Basic desc)
WHERE rownum = 1
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
==is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highestbasicsalary.
– APC
Nov 25 '18 at 20:22
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
add a comment |
Try this:
SELECT * FROM
(SELECT E.EmpCode, E.EmpName, E.DOB, E.DOJ, E.DeptCode, E.DesgCode, E.PhNo,
E.Qualification, E.Nationality, S.Basic, S.HRA, S.TA, S.UTA, S.OTRate
FROM EMP AS E JOIN SALARY AS S ON (E.EmpCode = S.EmpCode) order by S.Basic desc)
WHERE rownum = 1
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
==is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highestbasicsalary.
– APC
Nov 25 '18 at 20:22
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
add a comment |
Try this:
SELECT * FROM
(SELECT E.EmpCode, E.EmpName, E.DOB, E.DOJ, E.DeptCode, E.DesgCode, E.PhNo,
E.Qualification, E.Nationality, S.Basic, S.HRA, S.TA, S.UTA, S.OTRate
FROM EMP AS E JOIN SALARY AS S ON (E.EmpCode = S.EmpCode) order by S.Basic desc)
WHERE rownum = 1
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
Try this:
SELECT * FROM
(SELECT E.EmpCode, E.EmpName, E.DOB, E.DOJ, E.DeptCode, E.DesgCode, E.PhNo,
E.Qualification, E.Nationality, S.Basic, S.HRA, S.TA, S.UTA, S.OTRate
FROM EMP AS E JOIN SALARY AS S ON (E.EmpCode = S.EmpCode) order by S.Basic desc)
WHERE rownum = 1
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
edited Nov 26 '18 at 1:36
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
answered Nov 25 '18 at 18:28
KwakuCscKwakuCsc
194
194
==is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highestbasicsalary.
– APC
Nov 25 '18 at 20:22
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
add a comment |
==is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highestbasicsalary.
– APC
Nov 25 '18 at 20:22
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
== is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highest basic salary.– APC
Nov 25 '18 at 20:22
== is not valid Oracle syntax. Also this query will not produce a correct solution if there are two employees both earning the highest basic salary.– APC
Nov 25 '18 at 20:22
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
Thanks @APC for pointing out my mistakes
– KwakuCsc
Nov 26 '18 at 1:38
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%2f53470404%2foracle-sql-join-query-to-find-highest-salary%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
