some one help me to find out nth highest salary from the salary table in MYSQL
This question is related to
mysql
The answer is
Here we can create the MYSQL function for this. nth highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE limitv INT;
SET limitv = N - 1;
RETURN (
Select IFNULL(
(select Distinct Salary from Employee order by Salary Desc limit limitv, 1),
NULL
) as getNthHighestSalary
);
END
If you want to get all the records of the employees who has third highest salary then you can use this sql query:
Table name: salary
select * from salary where salary =
(select distinct salary from salary order by salary desc limit 2,1)
Try this, n would be the nth item you would want to return
SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC LIMIT n,1
This will work To find the nth maximum number
SELECT
TOP 1 * from (SELECT TOP nth_largest_no * FROM Products Order by price desc) ORDER BY price asc;
For Fifth Largest number
SELECT
TOP 1 * from (SELECT TOP 5 * FROM Products Order by price desc) ORDER BY price asc;
This is salary table
SELECT amount FROM salary
GROUP by amount
ORDER BY amount DESC
LIMIT n-1 , 1
Or
SELECT DISTINCT amount
FROM salary
ORDER BY amount DESC
LIMIT n-1 , 1
Let there be table salaries containing
+----------+--------+--------+
| emp | salary | deptno |
+----------+--------+--------+
| ep1 | 10 | dp1 |
| ep2 | 20 | dp2 |
| ep3 | 30 | dp2 |
| ep4 | 40 | dp1 |
| ep5 | 50 | dp1 |
| ep6 | 60 | dp3 |
| ep7 | 70 | dp3 |
+----------+--------+--------+
By Nested Queries: (where you can change offset 0/1/2... for first, second and third... place respectively)
select
*
from
salaries as t1
where
t1.salary = (select
salary
from
salaries
where
salaries.deptno = t1.deptno ORDER by salary desc limit 1 offset 1);
or might be by creating rank: (where you can change rank= 1/2/3... for first, second and third... place respectively)
SET @prev_value = NULL;
SET @rank_count = 0;
select * from
(SELECT
s.*,
CASE
WHEN @prev_value = deptno THEN @rank_count := @rank_count + 1
WHEN @prev_value := deptno THEN @rank_count := 1
ELSE @rank_count := 1
END as rank
FROM salaries s
ORDER BY deptno, salary desc) as t
having t.rank = 2;
if wanna specified nth highest,could use rank method.
To get the third highest, use
SELECT * FROM
(SELECT @rank := @rank + 1 AS rank, salary
FROM tbl,(SELECT @rank := 0) r
order by salary desc ) m
WHERE rank=3
Try this solution.
select SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT salary ORDER BY salary DESC),',',3),',',-1) from employees
SELECT * FROM employe e1 WHERE n-1 = ( SELECT COUNT(DISTINCT(e2.salary)) FROM employe e2 WHERE e2.salary > e1.salary)
Where n = highest number of salary like 1,2,3
Sorting all the records first, do consume a lot of time (Imagine if the table contains millions of records). However, the trick is to do an improved linear-search.
SELECT * FROM Employee Emp1
WHERE (N-1) = ( SELECT COUNT(*) FROM (
SELECT DISTINCT(Emp2.Salary)
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary LIMIT N))
Here, as soon as inner query finds n distinct salary values greater than outer query's salary, it returns the result to outer query.
Mysql have clearly mentioned about this optimization at http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html
The above query can also be written as,
SELECT * FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary LIMIT N)
Again, if the queries are as simple as just running on single table and needed for informational purposes only, then you could limit the outermost query to return 1 record and run a separate query by placing the nth highest salary in where clause
Thanks to Abishek Kulkarni's solution, on which this optimization is suggested.
To get nth highest salary you need to first sort data by using ORDER BY and then select the nth highest record using LIMIT with OFFSET.
SELECT DISTINCT(salary) AS salary
FROM tbl_salary
ORDER BY salary DESC
LIMIT 1 OFFSET (n - 1);
SET @cnt=0;
SELECT s.*
FROM (SELECT ( @cnt := @cnt + 1 ) AS rank,
a.*
FROM one AS a
ORDER BY a.salary DESC) AS s
WHERE s.rank = '3';
select distinct(column_name) from table_name order by column_name desc limit (n-1),1;
+-------+--------+
| name | salary |
+-------+--------+
| A | 100 |
| B | 200 |
| C | 300 |
| D | 400 |
| E | 500 |
| F | 500 |
| G | 600 |
+-------+--------+
IF YOU WANT TO SELECT ONLY Nth HIGHEST SALARY THEN:
SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET N-1;
IF YOU WANT TO SELECT ALL EMPLOYEE WHO GETTING Nth HIGHEST SALARY THEN:
SELECT * FROM emp WHERE salary = (
SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET N-1
);
SELECT DISTINCT(column_name)
FROM table_name
ORDER BY column_name DESC limit N-1,1;
where N represents the nth highest salary ..
Third highest salary :
SELECT DISTINCT(column_name)
FROM table_name
ORDER BY column_name DESC limit 2,1;
If you want to find nth Salary from a table (here n should be any thing like 1st or 2nd or 15th highest Salaries)
This is the Query for to find nth Salary:
SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET (n-1)
If you want to find 8th highest salary, query should be :
SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET 7
Note: OFFSET starts from 0th position, and hence use N-1 rule here
For 4th highest salary:
select min(salary) from (select distinct salary from hibernatepractice.employee e order by salary desc limit 4) as e1;
For n th highest salary:
select min(salary) from (select distinct salary from hibernatepractice.employee e order by salary desc limit n) as e1;
SELECT * FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return N-1.
To get 2nd highest salary:
SELECT salary
FROM [employees]
ORDER BY salary DESC
offset 1 rows
FETCH next 1 rows only
To get Nth highest salary:
SELECT salary
FROM [employees]
ORDER BY salary DESC
offset **n-1** rows
FETCH next 1 rows only
finding the highest salary
select MAX(Salary) from Employee;
finding the 2nd highest salary
Query-1
SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
Query-2
select MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee )
finding the nth highest salary
Query-1
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
Query-2
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
nth highest salary using the TOP keyword in SQL Server
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary
Find the nth highest salary in MySQL
SELECT Salary FROM Employee
ORDER BY Salary DESC LIMIT n-1,1
Find the nth highest salary in SQL Server
SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY
Find the nth highest salary in Oracle using rownum
select * from (
select Emp.*,
row_number() over (order by Salary DESC) rownumb
from Employee Emp
)
where rownumb = n; /*n is nth highest salary*/
Find the nth highest salary in Oracle using RANK
select * FROM (
select EmployeeID, Salary
,rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;
set @cnt=0;
select s.* from (SELECT (@cnt := @cnt + 1) AS rank,a.* FROM one as a order by a.salary DESC) as s WHERE s.rank='3';
=>this for 3rd highest salary. for nth replace 3 value. for example 5th highest:
set @cnt=0;
select s.* from (SELECT (@cnt := @cnt + 1) AS rank,a.* FROM one as a order by a.salary DESC) as s WHERE s.rank='5';
Try this one for finding 5th highest salary-
SELECT DISTINCT(column name) FROM table ORDER BY (column name) desc LIMIT 4,1
for nth salary-
SELECT DISTINCT(column name) FROM table ORDER BY (column name) desc LIMIT n-1,1
I have tried it on phpmyadmin panel..
MySQL query to find Nth highest salary from a salary table(100% true)
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1,1;
try this:
select MIN(sal) from salary where sal in
(select sal from salary order by sal desc limit 9)
The query to get the nth highest record is as follows:
SELECT
*
FROM
(SELECT
*
FROM
table_name
ORDER BY column_name ASC
LIMIT N) AS tbl
ORDER BY column_name DESC
LIMIT 1;
It's simple and easy to understand
Source: Stackoverflow.com