How to get employee name with minimum and maximum of salary for second employee name with same salar-CodePudding

I'm having a table odd_empcop in which minimum salary = 2000 and maximum salary =8000 , there are many employees having maximum salary of 8000. I want to get the employee name with minimum salary and maximum salary of second employee.

I've applied this code:

(select emp_name, salary from odd_empcop
where salary = (select min(salary) from odd_empcop
                    order by salary ))
union
(select  emp_name, salary from odd_empcop
where salary = (select max(salary) from odd_empcop
    
                order by salary desc ));

getting output like:

emp_name salary
Gautham  2000
Melinda  8000
Cory     8000
Vikram   8000

But I want to get the output as:

emp_name salary
Gautham  2000
Cory     8000

I'VE GOT IT!!!!

(select emp_name, salary from odd_empcop
where salary = (select min(salary) from odd_empcop
                    order by salary ))
union
(select  emp_name, salary from odd_empcop
where salary = (select max(salary) from odd_empcop

                order by salary desc ) limit 1,1);

CodePudding user response：

You can use the ROW_NUMBER analytic function to get the details of the employee with the lowest salary and the second employee when ordered by descending salary and then by name:

SELECT emp_name,
       salary
FROM   (
  SELECT emp_name,
         salary,
         ROW_NUMBER() OVER (ORDER BY salary ASC,  emp_name ASC) AS rn_min,
         ROW_NUMBER() OVER (ORDER BY salary DESC, emp_name ASC) AS rn_max
  FROM   odd_empcop
) e
WHERE  rn_min = 1
OR     rn_max = 2;

Which, for the sample data:

CREATE TABLE odd_empcop (
  salary NUMERIC(10,2),
  emp_name VARCHAR(20)
);

INSERT INTO odd_empcop (emp_name, salary)
SELECT 'Gautham', 2000 FROM DUAL UNION ALL
SELECT 'Melinda', 8000 FROM DUAL UNION ALL
SELECT 'Cory',    8000 FROM DUAL UNION ALL
SELECT 'Vikram',  8000 FROM DUAL;

Outputs:

emp_name salary

Melinda 8000.00

Gautham 2000.00

emp_name	salary
Melinda	8000.00
Gautham	2000.00

MySQL db<>fiddle Oracle db<>fiddle

CodePudding user response：

You can achieve this by using LIMIT.

select emp_name, salary from odd_empcop where salary=(select max(salary) from odd_empcop order by salary DESC LIMIT 1, 1)

For example.

CodePudding user response：

I believe MYSQL supports a Limit clause (similar to SQLs TOP clause). Maybe try something like union (select emp_name, salary from odd_empcop where salary = (select max(salary) from odd_empcop limit 1