1

Suppose we have some employees in each department.we have total 3 departments . Below is the sample source table named 'employee'

emp dept_id salary
A    10     1000
B    10     2000
C    10     3000
D    20     7000
E    20     9000
F    20     8000
G    30     17000
H    30     15000
I    30     30000
j    30     30000
k    30     17000

Here may same salary is exist in same department.

I use Wamp-server which has mysql-5.7.23

And I want to like:

B    10     2000
F    20     8000
G    30     17000
Sayem
  • 104
  • 11
  • may be your question is wrong because you mentioned "same salary is exist in same department " using that statement , you want output only related to department id 30 – Manish sharma May 02 '19 at 11:44

1 Answers1

2

I think there are several way to solve the problem. Following solution from my side and works fine.

SELECT *
From employee e2 
WHERE e2.salary = (SELECT distinct salary FROM employee where dept_id=e2.dept_id order by salary desc limit 1,1);

I need only second highest salary value with department wise which is the input array of next operation in my project. Finally I use

SELECT e2.dept_id, max(e2.salary) From employee e2 WHERE e2.salary = (SELECT distinct salary FROM employee where dept_id=e2.dept_id order by salary desc limit 1,1) group by e2.dept_id

Sayem
  • 104
  • 11
  • The `group by` is not required for this query (and in fact will cause it to fail on many MySQL installations due to a violation of sql mode `only_full_groupby`) – Nick May 02 '19 at 11:00
  • In this case without group by the result shown like: B 10 2000 F 20 8000 G 30 17000 k 30 17000 so what do you think? @Nick – Sayem May 02 '19 at 11:07
  • If the target is employee data, then you are right. But here I need only one data that the salary is 17000 which is the input of next operation as my project needs. – Sayem May 02 '19 at 11:17
  • Then you could just change the query to `SELECT DISTINCT dept_id, salary` and you could still remove the `group by`. Otherwise you could keep the `group by` and change to `SELECT dept_id, MIN(salary)` (or `MAX`, since any values would be identical it doesn't matter) – Nick May 02 '19 at 11:25
  • You could simply join by emp instead of salary if you want exactly one row. – Salman A May 02 '19 at 11:45