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?
This question is related to
mysql
SELECT name,salary FROM employee
WHERE salary = (SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 1,1) ORDER BY name
SELECT name, salary
FROM EMPLOYEES
WHERE salary = (
SELECT DISTINCT salary
FROM EMPLOYEES
ORDER BY salary DESC
LIMIT 1 , 1 )
simple solution
SELECT * FROM TBLNAME ORDER BY COLNAME ASC LIMIT (n - x), 1
Note: n = total number of records in column
x = value 2nd, 3rd, 4th highest etc
e.g
//to find employee with 7th highest salary
n = 100
x = 7
SELECT * FROM tbl_employee ORDER BY salary ASC LIMIT 93, 1
hope this helps
To display records having second largest value of mark:
SELECT username, mark
FROM tbl_one
WHERE mark = (
SELECT DISTINCT mark
FROM tbl_one
ORDER by mark desc
LIMIT 1,1
);
SELECT MIN(id) as id FROM students where id>(SELECT MIN(id) FROM students);
The simple solution is as given below in query:
select max(salary) as salary from employees where salary<(select max(salary) from employees);
Try this :
SELECT DISTINCT(`salary`)
FROM `employee`
ORDER BY `salary` DEC
LIMIT 1,1
To get the *N*th highest value, better to use this solution:
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.salary );
N=2 for second highest N=3 for third highest and so on.
A straight forward answer for second highest salary
SELECT name, salary
FROM employees ORDER BY `employees`.`salary` DESC LIMIT 1 , 1
another interesting solution
SELECT salary
FROM emp
WHERE salary = (SELECT DISTINCT(salary)
FROM emp as e1
WHERE (n) = (SELECT COUNT(DISTINCT(salary))
FROM emp as e2
WHERE e1.salary <= e2.salary))
SELECT DISTINCT Salary
FROM emp
ORDER BY salary DESC
LIMIT 1 , 1
This query will give second highest salary of the duplicate records as well.
with alias as
(
select name,salary,row_number() over(order by salary desc ) as rn from employees
)
select name,salary from alias where rn=n--n being the nth highest salary
SELECT name, salary
FROM employees
order by salary desc limit 1,1
and this query should do your job.
First we are sorting the table in descending way so the person with the highest salary is at the top, and the second highest is at the second position. Now limit a,b
means skip the starting a
elements and then print the next b
elements. So you should use limit 1,1
in this case.
Hope this helps.
FOR SECOND LAST:
SELECT name, salary
FROM employee
ORDER BY salary DESC
LIMIT 1 , 1
FOR THIRD LAST:
SELECT name, salary
FROM employee
ORDER BY salary DESC
LIMIT 2 , 1
Seems I'm much late to answer this question. How about this one liner to get the same output?
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1,1 ;
sample fiddle: https://www.db-fiddle.com/f/v4gZUMFbuYorB27AH9yBKy/0
Try this :
Proc sql;
select employee, salary
from (select * from test having salary < max(salary))
having salary = max(salary)
;
Quit;
SELECT username, salary
FROM tblname
GROUP by salary
ORDER by salary desc
LIMIT 0,1 ;
SELECT name, salary
FROM employees
where
salary = (SELECT (salary) FROM employees GROUP BY salary DESC LIMIT 1,1)
for 2nd heightest salary
select max(salary) from salary where salary not in (select top 1 salary from salary order by salary desc)
for 3rd heightest salary
select max(salary) from salary where salary not in (select top 2 salary from salary order by salary desc)
and so on......
SELECT SALARY
FROM (SELECT *
FROM EMPLOYEE
ORDER BY SALARY
DESC LIMIT ***2***) AS TOP_SALARY
ORDER BY SALARY ASC
LIMIT 1
To get the second highest salary just use the below query
SELECT salary FROM employees
ORDER BY salary DESC LIMIT 1,1;
Found another interesting solution
SELECT salary
FROM emp
WHERE salary = (SELECT DISTINCT(salary)
FROM emp as e1
WHERE (n) = (SELECT COUNT(DISTINCT(salary))
FROM emp as e2
WHERE e1.salary <= e2.salary))
Sorry. Forgot to write. n is the nth number of salary which you want.
You can use this below mentioned query
SELECT emp.name, emp.salary
FROM employees emp
WHERE 2 = (SELECT COUNT(DISTINCT salary)
FROM employees
WHERE emp.salary<=salary
);
You can change 2 to your desired highest record.
Try this one to get n th max salary
i have tried this before posting & It Works fine
eg. to find 10th max salary replace limit 9,1;
mysql> select name,salary from emp group by salary desc limit n-1,1;
To get second highest value:
SELECT `salary` FROM `employees` ORDER BY `salary` DESC LIMIT 1, 1;
Get second, third, fourth......Nth highest salary using following query
SELECT MIN(salary) from employees WHERE salary IN( SELECT TOP N salary FROM employees ORDER BY salary DESC)
Replace N by you number i.e. N=2 for second highest salary, N=3 for third highest salary and so on. So for second highest salary use
SELECT MIN(salary) from employees WHERE salary IN( SELECT TOP 2 salary FROM employees ORDER BY salary DESC)
create table svalue (
name varchar(5),
value int
) engine = myisam;
insert into svalue value ('aaa',30),('bbb',10),('ccc',30),('ddd',20);
select * from svalue where value = (
select value
from svalue
group by value
order by value desc limit 1,1)
SELECT MAX(salary) salary
FROM tbl
WHERE salary <
(SELECT MAX(salary)
FROM tbl);
Source: Stackoverflow.com