How to get employee name with minimum and maximum of salary for second employee name with same salary value in mysql?
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
SQL GROUP BY and MIN to find people with minimum salary in each department
Instead fo a IN clause you can also use an inner join
SELECT first_name, last_name, salary, department_id
FROM employees
INNER JOIN ( SELECT department_id, MIN(salary) min_sal
FROM employees
GROUP BY department_id
) t on t.department_id =employees.department_id
and employees.salary = t.min_sal;this should be better for performance
How to get the maximum and minimum salary of all the departments along with department name,manager id, manager name of each department?
You have to make two JOINs to the employees table : first join between employee and manager_id, and second join between employee and department_id.
Something like this should work:
SELECT
d.department_id,
d.department_name,
d.manager_id,
m.first_name,
max(e.salary) as Maximum_Salary,
min(e.salary) Minimum_Salary
FROM departments d
LEFT JOIN employees e ON d.department_id=e.department_id
LEFT JOIN employees m ON d.manager_id = m.employee_id
GROUP BY d.department_id, d.department_name,
d.manager_id, m.first_name
ORDER BY d.department_id;
Related Topics
How to Include Results of SQL Count If Count=0
How to Convert This SQL Select to Linq Query
Convert Utc Milliseconds to Datetime in SQL Server
Best Way to Iterate Through Columns in a SQL Table
Mysql - Left Join Takes Too Long, How to Optimize Query
Regex to Filter for Numers With and Without Dots
How to Sum Up Time Field in SQL Server
Sql Query to Get Number of Times a Field Repeats for Another Specific Field
Postgres Query to Check a String Is a Number
Mysql Split Comma Separated String into Temp Table
Postgresql Query to Return Results as a Comma Separated List
Select Count of Total Products as Well as Out of Stock Products from Table
Sql Join: Selecting the Last Records in a One-To-Many Relationship
How to Get a List of Column Names on Sqlite3 Database