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
how to find two highest salary from every department, your suggestion should be appreciated.
Refer this post for more informations
Executed the above query and get the correct result but as performance wise its not correct ,Is there any other way of query to get the Nth Highest value.
Refer different methods suggested here
public DataTable retrieve()
{
SqlCommand cmdret=new SqlCommand(“SELECT TOP 1 salary FROM(SELECT DISTINCT top 2 salary FROM tb_connect ORDER BY salary )a ORDER BY salary desc “,constr);
SqlDataAdapter da=new SqlDataAdapter(cmdret);
DataSet dts=new DataSet();
try
{
da.Fill(dts,”tb_connect”);
return dts.Tables[“tb_connect”];
}
catch
{
throw;
}
finally
{
constr.Close();
}
it will gives second lowest salary
highest 4 th salary from tb_connect table
SqlCommand cmdret=new SqlCommand
(“SELECT TOP 1 salary FROM
(SELECT DISTINCT top 4 salary FROM tb_connect ORDER BY salary desc )
a ORDER BY salary “,constr);
Hi,
I have 2 tables
Staff
——
PID (primary key)
Firstname
LastName
Country
Postal Code
City
Street
DateofBirth
Salary
TelephoneNo
——————-
PID (primary key)
TelephoneNo (also primary key)
My question is -> Give a list of telephone number of Susan Winter (Remark: don’t specify the internal PID!- this confused me )
Can anyone help solve this please. i am beginner to SQL.
This forum helped me a lot. Thanks every one who posted :-)
@Tash
Didn;t get that question, Is this what you are looking ?
Select TelephoneNo
From Staff A
join TelephoneNo B on A.PID = B.PID
Where A.FirstName +’ ‘+A.LastName = @Name
~IM.
———————————Try this———————————
select distinct(e.salary) from
(select salary,dense_rank() over(order by salary desc)R from EMP)e
where e.R=3
———————————Try this———————————
———————————For Nth max———————————
select distinct(e.salary) from
(select salary,dense_rank() over(order by salary desc)R from EMP)e
where e.R=N
Any answer this question?
In oracle “TOP” keyword is there or not. And it is working in SQL queries.
Advance thanks
sreenivas
TOP is not supported in Oralce. You need to make use of rownum column to limit the data
Great
Gud logic
hi Pinal,
i want to know how to find the second highest salary for each department from emp table. if we have table like this:
name dept_name salary
——– ————— ———
raj Account 15000
sam Account 20000
roy Admin 8500
paul Admin 9000
jack Admin 12000
ram IT 25000
sir
i want to top 10 highest salary from employee table ( for ex: 100 rows of employee table( top 10 salary ))
Refer this and use 10 in place of 5
the very easy answer is ( in mysql )
select salary from employee order by salary desc limit 1
by- balwant bishnoi
Sometime the Basic of SqlQuery get Confused me
I try your above query
by creating following db
dbo.Test
Create Table Test (Name Varchar(50), Salary Numeric(18,2))
Insert Into Test (Name,Salary) Values (‘A’, 100)
Insert Into Test (Name,Salary) Values (‘B’, 90)
Insert Into Test (Name,Salary) Values (‘C’, 80)
Insert Into Test (Name,Salary) Values (‘D’, 70)
Insert Into Test (Name,Salary) Values (‘E’, 60)
Insert Into Test (Name,Salary) Values (‘F’, 50)
Insert Into Test (Name,Salary) Values (‘G’, 40)
Insert Into Test (Name,Salary) Values (‘H’, 30)
Insert Into Test (Name,Salary) Values (‘I’, 20)
Now as Per your above query i fired the same in my ssms
i get the result
(6th Highest)
50
but the query seems to be confuse me
SELECT dISTINCT TOP 6
SALARY
FROM TEST
ORDER BY SALARY DESC
100.00
90.00
80.00
70.00
60.00
50.00
now apply your query method
SELECT TOP 1 SALARY FROM(
SELECT dISTINCT TOP 6
SALARY
FROM TEST
ORDER BY SALARY DESC
) A
ORDER BY SALARY
i get the result 50
but in subquery i get the following ans
100.00
90.00
80.00
70.00
60.00
50.00
then how can top 1 work because in subquery 100 is first na then why we get the result 50. Please help me
i have a doubt in sql.
we have a employee table and having fields empno, empname, and managername. i want to retrieve managername who has more than 50 employees working under him
pls anybody knows pls send me
Following is the simple query to get the 3rd highest salary.
select distinct min(sal) from (select sal from(select sal from employee order by sal desc)where rownum<=3)
Instead of 3, we can substitute n values.
rownum is for ORACLE. You need to use row_number() function as described in this post at point 6
@sai
Following query will retrieve the managername who has more than 50 employees working under him.
select managername from employee group by managername having count(*)>50;
this worked for me :
select top 5 Salary from (SELECT TOP 5 * FROM employeetable ORDER BY employeetable.salary DESC) a ORDER BY salary
thanks Pinal Dave for this!!!
hey sir i have a query here which worked but i don’t understand how….please help me
mysql> select * from emp a
where 2>(select count( distinct(sal))from emp where sal>a.sal and a.deptno=deptno )
order by deptno;
it is the query to find two highest salaries in every department
hi its very nice thanks