Getting second highest Salary value in MySQL
In a given question,
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
I wrote a code for this question, however, the expected output is different.
My Code:
SELECT
CASE
WHEN COUNT(*) = 1 THEN NULL
ELSE (SELECT Salary FROM Employee HAVING Salary < MAX(Salary) ORDER BY Salary DESC LIMIT 1)
END AS SecondHighestSalary
FROM Employee;
I think there's something wrong with my code but I cannot find what is the actual problem here. My code returns 100
not 200
. What have I gotten wrong?
+---------------------+
| SecondHighestSalary |
+---------------------+
| 100 |
+---------------------+
mysql sql subquery
add a comment |
In a given question,
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
I wrote a code for this question, however, the expected output is different.
My Code:
SELECT
CASE
WHEN COUNT(*) = 1 THEN NULL
ELSE (SELECT Salary FROM Employee HAVING Salary < MAX(Salary) ORDER BY Salary DESC LIMIT 1)
END AS SecondHighestSalary
FROM Employee;
I think there's something wrong with my code but I cannot find what is the actual problem here. My code returns 100
not 200
. What have I gotten wrong?
+---------------------+
| SecondHighestSalary |
+---------------------+
| 100 |
+---------------------+
mysql sql subquery
You can get nth highest record visit stackoverflow.com/a/26732513/2893413
– Sadikhasan
Nov 21 '18 at 6:57
add a comment |
In a given question,
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
I wrote a code for this question, however, the expected output is different.
My Code:
SELECT
CASE
WHEN COUNT(*) = 1 THEN NULL
ELSE (SELECT Salary FROM Employee HAVING Salary < MAX(Salary) ORDER BY Salary DESC LIMIT 1)
END AS SecondHighestSalary
FROM Employee;
I think there's something wrong with my code but I cannot find what is the actual problem here. My code returns 100
not 200
. What have I gotten wrong?
+---------------------+
| SecondHighestSalary |
+---------------------+
| 100 |
+---------------------+
mysql sql subquery
In a given question,
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
I wrote a code for this question, however, the expected output is different.
My Code:
SELECT
CASE
WHEN COUNT(*) = 1 THEN NULL
ELSE (SELECT Salary FROM Employee HAVING Salary < MAX(Salary) ORDER BY Salary DESC LIMIT 1)
END AS SecondHighestSalary
FROM Employee;
I think there's something wrong with my code but I cannot find what is the actual problem here. My code returns 100
not 200
. What have I gotten wrong?
+---------------------+
| SecondHighestSalary |
+---------------------+
| 100 |
+---------------------+
mysql sql subquery
mysql sql subquery
edited Nov 21 '18 at 8:26
Strawberry
26.7k83250
26.7k83250
asked Nov 21 '18 at 6:50
Backrub32Backrub32
373322
373322
You can get nth highest record visit stackoverflow.com/a/26732513/2893413
– Sadikhasan
Nov 21 '18 at 6:57
add a comment |
You can get nth highest record visit stackoverflow.com/a/26732513/2893413
– Sadikhasan
Nov 21 '18 at 6:57
You can get nth highest record visit stackoverflow.com/a/26732513/2893413
– Sadikhasan
Nov 21 '18 at 6:57
You can get nth highest record visit stackoverflow.com/a/26732513/2893413
– Sadikhasan
Nov 21 '18 at 6:57
add a comment |
5 Answers
5
active
oldest
votes
You can utilize LIMIT {[offset,] row_count}
. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html
Order by Salary
in descending order, and get the second row by defining OFFSET
as 1. We will use DISTINCT
on Salary
as there is a possibility to have multiple rows for the highest salary.
SELECT DISTINCT
Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
Thank you for your answer. But I tried this and this does not returnnull
when there's only one employee. I think maybe addingIFNULL()
would do?
– Backrub32
Nov 21 '18 at 7:20
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to gettingnull
. You can handle this in application code (eg: PHP), when no rows are returned.
– Madhur Bhaiya
Nov 21 '18 at 7:26
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
|
show 3 more comments
You can try below
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
add a comment |
Try that out:
SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1
or to a deeper approach you can use something like:
SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1
All that queries have high performance, since they does not have any subqueries.
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
add a comment |
If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown
select (select salary
from Employee ORDER BY salary DESC LIMIT 1,1
) as x
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
add a comment |
Here is the solution.
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
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%2f53406649%2fgetting-second-highest-salary-value-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can utilize LIMIT {[offset,] row_count}
. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html
Order by Salary
in descending order, and get the second row by defining OFFSET
as 1. We will use DISTINCT
on Salary
as there is a possibility to have multiple rows for the highest salary.
SELECT DISTINCT
Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
Thank you for your answer. But I tried this and this does not returnnull
when there's only one employee. I think maybe addingIFNULL()
would do?
– Backrub32
Nov 21 '18 at 7:20
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to gettingnull
. You can handle this in application code (eg: PHP), when no rows are returned.
– Madhur Bhaiya
Nov 21 '18 at 7:26
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
|
show 3 more comments
You can utilize LIMIT {[offset,] row_count}
. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html
Order by Salary
in descending order, and get the second row by defining OFFSET
as 1. We will use DISTINCT
on Salary
as there is a possibility to have multiple rows for the highest salary.
SELECT DISTINCT
Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
Thank you for your answer. But I tried this and this does not returnnull
when there's only one employee. I think maybe addingIFNULL()
would do?
– Backrub32
Nov 21 '18 at 7:20
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to gettingnull
. You can handle this in application code (eg: PHP), when no rows are returned.
– Madhur Bhaiya
Nov 21 '18 at 7:26
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
|
show 3 more comments
You can utilize LIMIT {[offset,] row_count}
. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html
Order by Salary
in descending order, and get the second row by defining OFFSET
as 1. We will use DISTINCT
on Salary
as there is a possibility to have multiple rows for the highest salary.
SELECT DISTINCT
Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1
You can utilize LIMIT {[offset,] row_count}
. Refer https://dev.mysql.com/doc/refman/8.0/en/select.html
Order by Salary
in descending order, and get the second row by defining OFFSET
as 1. We will use DISTINCT
on Salary
as there is a possibility to have multiple rows for the highest salary.
SELECT DISTINCT
Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1
edited Nov 21 '18 at 7:23
answered Nov 21 '18 at 6:52
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
Thank you for your answer. But I tried this and this does not returnnull
when there's only one employee. I think maybe addingIFNULL()
would do?
– Backrub32
Nov 21 '18 at 7:20
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to gettingnull
. You can handle this in application code (eg: PHP), when no rows are returned.
– Madhur Bhaiya
Nov 21 '18 at 7:26
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
|
show 3 more comments
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
Thank you for your answer. But I tried this and this does not returnnull
when there's only one employee. I think maybe addingIFNULL()
would do?
– Backrub32
Nov 21 '18 at 7:20
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to gettingnull
. You can handle this in application code (eg: PHP), when no rows are returned.
– Madhur Bhaiya
Nov 21 '18 at 7:26
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
@Sadikhasan how does it even matter, as OP only wants the salary figure, not the full row.
– Madhur Bhaiya
Nov 21 '18 at 6:56
Thank you for your answer. But I tried this and this does not return
null
when there's only one employee. I think maybe adding IFNULL()
would do?– Backrub32
Nov 21 '18 at 7:20
Thank you for your answer. But I tried this and this does not return
null
when there's only one employee. I think maybe adding IFNULL()
would do?– Backrub32
Nov 21 '18 at 7:20
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 hmm.. let me test. I havent tested it.
– Madhur Bhaiya
Nov 21 '18 at 7:23
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to getting
null
. You can handle this in application code (eg: PHP), when no rows are returned.– Madhur Bhaiya
Nov 21 '18 at 7:26
@Poream3387 it returns no results: db-fiddle.com/f/e1Efjj6SY5xVrTEuJ9oP4j/0 - It is pretty much equivalent to getting
null
. You can handle this in application code (eg: PHP), when no rows are returned.– Madhur Bhaiya
Nov 21 '18 at 7:26
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
Oh, got it thanks!
– Backrub32
Nov 21 '18 at 8:30
|
show 3 more comments
You can try below
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
add a comment |
You can try below
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
add a comment |
You can try below
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
You can try below
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
answered Nov 21 '18 at 6:53
fa06fa06
16.8k21018
16.8k21018
add a comment |
add a comment |
Try that out:
SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1
or to a deeper approach you can use something like:
SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1
All that queries have high performance, since they does not have any subqueries.
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
add a comment |
Try that out:
SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1
or to a deeper approach you can use something like:
SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1
All that queries have high performance, since they does not have any subqueries.
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
add a comment |
Try that out:
SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1
or to a deeper approach you can use something like:
SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1
All that queries have high performance, since they does not have any subqueries.
Try that out:
SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1
or to a deeper approach you can use something like:
SELECT salary FROM Employee GROUP BY salary ORDER BY salary DESC LIMIT 1,1
All that queries have high performance, since they does not have any subqueries.
edited Nov 21 '18 at 7:11
answered Nov 21 '18 at 6:58
rod.poli.dinizrod.poli.diniz
116118
116118
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
add a comment |
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
how it will work without order by clause
– fa06
Nov 21 '18 at 7:02
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
You was right fa06, I just edited.
– rod.poli.diniz
Nov 21 '18 at 7:12
add a comment |
If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown
select (select salary
from Employee ORDER BY salary DESC LIMIT 1,1
) as x
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
add a comment |
If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown
select (select salary
from Employee ORDER BY salary DESC LIMIT 1,1
) as x
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
add a comment |
If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown
select (select salary
from Employee ORDER BY salary DESC LIMIT 1,1
) as x
If you want to display a empty_row(null) in case there is no highest salary then the following does it, if there is a value then it gets shown
select (select salary
from Employee ORDER BY salary DESC LIMIT 1,1
) as x
answered Nov 21 '18 at 8:21
George JosephGeorge Joseph
1,590510
1,590510
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
add a comment |
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
I don't understand the point of the wrapper
– Strawberry
Nov 21 '18 at 8:23
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
The OP wanted to return a row with "null" value in case there is no second highest salary.. Without the "wrapper, the query would return "no_rows_returned" or an empty record set.
– George Joseph
Nov 21 '18 at 8:33
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
Right, got it!!
– Strawberry
Nov 21 '18 at 9:30
add a comment |
Here is the solution.
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
add a comment |
Here is the solution.
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
add a comment |
Here is the solution.
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
Here is the solution.
SELECT MAX(salary) From Employee WHERE salary < ( SELECT Max(salary) FROM Employee);
answered Nov 21 '18 at 9:20
Sahil AnandSahil Anand
1395
1395
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%2f53406649%2fgetting-second-highest-salary-value-in-mysql%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
You can get nth highest record visit stackoverflow.com/a/26732513/2893413
– Sadikhasan
Nov 21 '18 at 6:57