Menu
I have a table of employees and salaries defined that way:
"name" (type: VARCHAR) "salary" (type: INTEGER) What query can I use to get the second highest salary in this table?
944
SELECT * FROM `employees` WHERE salary = (SELECT DISTINCT(salary) FROM `employees` ORDER BY salary DESC LIMIT {N-1},1); or you can try with: SELECT * FROM `employees` e1 WHERE (N-1) = (SELECT COUNT(DISTINCT(salary)) FROM `employees` e2 WHERE e1. salary < e2.
SELECT MAX (column_name) FROM table_name WHERE column_name NOT IN (SELECT Max (column_name) FROM table_name); First we selected the max from that column in the table then we searched for the max value again in that column with excluding the max value which has already been found, so it results in the 2nd maximum value.
The most simple way that should work in any database is to do following: SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 1 OFFSET 2; Which orders employees by salary and then tells db to return a single result (1 in LIMIT) counting from third row in result set (2 in OFFSET).
Here's one that accounts for ties.
Name Salary Jim 6 Foo 5 Bar 5 Steve 4 SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)) Result --> Bar 5, Foo 5 EDIT: I took Manoj's second post, tweaked it, and made it a little more human readable. To me n-1 is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.
/* looking for 2nd highest salary -- notice the '=2' */ SELECT name,salary FROM employees WHERE salary = (SELECT DISTINCT(salary) FROM employees as e1 WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM employees as e2 WHERE e1.salary <= e2.salary)) ORDER BY name Result --> Bar 5, Foo 5 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
