This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.
Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER – Query to Retrieve the Nth Maximum value
I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database Table
Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.
For particular example of employee :
How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table
The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary
where n > 1 (n is always greater than one)
Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks; GO SELECT TOP 1 Rate FROM ( SELECT DISTINCT TOP 4 Rate FROM HumanResources.EmployeePayHistory ORDER BY Rate DESC) A ORDER BY Rate GO
Reference : Pinal Dave (https://blog.sqlauthority.com), Pravin Phatangare
338 Comments. Leave new
Hi all,
Help me to write an sql query ..
suppose i have 4 departments. I need max salary in department(1) and 2nd highest salary in department(2) and third highest in dept(3) and least sal in 4th dept.
my table is like this
name sal dept
A 1000 1
B 1600 1
C 950 1
D 1050 1
E 960 2
F 800 2
G 840 2
H 1010 2
I 999 3
J 970 3
K 1000 3
L 940 3
M 1200 4
N 1050 4
P 888 4
R 799 4
I WANT THE OUTPUT LIKE THIS:
NAME SAL DEPT
B 1600 1
E 960 2 — SECOND HIGHEST IN DEPT
J 970 3 — THIRD HIGHEST
R 799 4 — 4TH HIGHEST
Is it possible to do it in SQL or we have to go for PLSQL. Please reply..
thanks in advance….
SELECT name, salary from emp order by salary desc limit 0, 1
This is specific to MySQL. But this site is for SQL Server
select ename,sal from (select ename,sal from emp order by sal desc) where rownum<=1;
Second Last Greater Number
SELECT GrandTotal FROM
( SELECT GrandTotal,ROW_NUMBER() OVER(ORDER BY GrandTotal desc) As RowNum
FROM def_ServiceProvider ) As A
WHERE A.RowNum IN (2,3)
Consider you want the second highest salary.
———————————————–
DECLARE @nth int;
SET @nth=2;
———————————————–
This example returns the nth highest salary from the employee table if the nth highest salary exists; else it returns -1.
———————————————–
SELECT (CASE WHEN COUNT(*)=@nth THEN MIN(salary) ELSE -1 END) AS salary
FROM
(
SELECT DISTINCT TOP (@nth) salary
FROM Employee
ORDER BY salary DESC
) a
———————————————–
NOTE: If you want to return NULL, replace -1 for NULL.
how we will run this query “Select min(Employee_Salary) from Employee_Test where
Employee_Salary IN(Select Distinct top 2 Employee_Salary from Employee_Test order by Employee_Salary desc );” in my sql as my sql does not support top keyword
best ewample yar
Hi PINAL DAVE
what is “a” in query.
its an Alias name.
can someone let me the query to retrieve the top 5 salary for a group of people. the out put should bee 5 records. For example in a 100 records, if 5 users have top 5 salaries.
/*for 3rd highest salary n=2, for 2nd highest salary n=1, nth highest salary n=n-1*/
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT n,1
Hi Pinal
Select DISTINCT A.CSTCOD,A.PONUMB,Rank() OVER (PARTITION by ITMCOD ORDER BY PODATE,RATAMT,A.PONUMB) AS Rnk,
PODATE,ITMCOD,ITMNAM,PURUOM,PURQTY,RATAMT,VALAMT From INPOBTBL A
Inner Join
(Select CSTCOD,PONUMB, PODATE From INPOHTBL Where CSTCOD=13 and PODATE between 20140201 and 20140228 ) B On
A.CSTCOD=b.CSTCOD And A.PONUMB=B.PONUMB
WHERE ITMCOD in (20018,20007)
From the above query out put i need to get only the record which is having the max of RNK Column . first thing i wanted to know is my approach is correct if yes how can i get the row where Rank is Max when am doing OVER (PARTITION by ITMCOD ORDER BY PODATE,RATAMT,A.PONUMB).
Thank you
Hi! Can you update a general sql query to find the nth highest or lowest in each group without using any analytics function or TOP
For Ex- Find the nth highest salary within each department . Without using ROW_NUMBER or ROWNUM or TOP or such functions or pseudo columns
emp table have emp details exept sal,sal table have sal how to find the 2 nd highest sa
Thrilok, your question is not clear to me.
selct person name form person where income <100000 and area name person name
Mayur – your question is not clear to me.
Sir i have one question with you. How we can get maximum salary of employee in given table. We dont need one. We need result of those persons who are getting maximum.
TableName: EMP_MGR
Emp_ID Emp_Name Salary Mgr_ID
1 Aman 45000 NULL
2 Deepak 35000 1
3 Pankaj 32000 1
4 Sapna 25000 1
5 Rajan 50000 NULL
6 Nupur 18000 5
7 Anamika 18000 5
8 Preet 22000 NULL
9 Shalu 27000 NULL
10 Jyoti 12000 9
11 Omesh 25000 9
12 Rakesh 21000 9
SELECT MAX(salary),E1.mgr_id FROM EMP_MGR E1,
(SELECT MAX(salary) AS sal,mgr_id FROM EMP_MGR GROUP BY mgr_id ) E2
WHERE E1.salary<E2.sal and E1.mgr_id = E2.mgr_id GROUP BY E1.mgr_id;
I want to find 2nd Highest record from each group including Managers(NULL group)
Expected Result:
Mgr_ID Salary
NULL 45000
1 32000
5 18000
9 15000
SELECT salary FROM employee ORDER BY salary DESC LIMIT 1
what is ‘a’ means in the query i don’t understand it
How to fetch the nth highest salary from a table without using TOP and sub-query?
IF you use using version 2005 or later, try using row_number() function
select t1.* from(
select *,row_number() over (order by salary desc) as sno from table
) as t
where sno=1