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 Pinal,
It’s really clean and good code for finding nth number of highest salary.
Thanks
Ravi
There are many methods. Refer this post
hiii sir i have a query to find nth salary from employee table
its working correctly
please explain me how it is working
thanks
select distinct salary
from employee e
where
4=(select count(distinct salary) from employee where e.salary<=salary);
please explain the working of this query
nitendra kumar
It just assigns the serial number based on the highest salary and picks up the 4th highest salary
hey i have a ques…
i have a table with studentdetails having columns id,name,technology.. and another table course having column name technology,count..
the 2nd table course do not have any values whenever a row is inserted in studentdetails the corresponding technology is inserted in course table n if it is already present its count shud be updated … i need to create trigger for this query,
Why do you need a trigger for this? Just use a select statement
select technology,count(*) as tech_count from studentdetails
group by technology
SELECT * FROM (SELECT sal FROM emp ORDER BY sal DESC LIMIT 3) emp ORDER BY sal ASC LIMIT 3;
Thnx himanshu.. compact querry with desired result
can we type in this following querry to find the second max salary from employee table
select max(salary-1) from employee;
how is this query different from above mentioned queries to find the second max salary from employee table??
please reply
thanks
Pramod
No, you cannot. Because if you use this syntax, you may get salary-1
3rd highest salary:
select max(sal) from emp
where sal < (select max(sal) from emp
where sal < (select max(sal) from emp))
santanu, this is ineffecient. What if you want to find 10th highestest? You need to repeat the code 10 times. Easier methods are described here
Hi,
My name is Bernard from Brooklyn New York. The following example I used a CTE along with a windowing/ranking function in an attempt to solve the problem.
Creat table sal
(ID INT IDENTITY PRIMARY KEY,
LNAME VARCHAR(25) NOT NULL,
FNAME VARCHAR(25) NOT NULL,
SAL INT NOT NULL
)
GO
–USE A ROW CONSTRUCTOR FUNCTION TO ADD THE DATA INTO THE TABLE
INSERT INTO SAL
(VALUES
(‘WILLIAMS’,’JOHN’,36000),
(‘STONE’,’BILLY’,45000),
(‘RAMSEY’,’PAULA’,75000),
(‘MURPHY’,’BERNARD’,100000));
WITH NARDO
AS
(SELECT EMPNO, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS ‘NTHSALARY’
FROM SAL
)
SELECT EMPNO, SAL, NTHSALARY
FROM NARDO
WHERE NTHSALARY = 2
Hi Bernard,
This is Dhirendra, in Sql server 2008 the insert statement is not working properly as per your query otherwise the concept of using Common Table Expression(CTE) is quite good. You can insert data in this manner too:
Create table sal
(ID INT IDENTITY PRIMARY KEY,
LNAME VARCHAR(25) NOT NULL,
FNAME VARCHAR(25) NOT NULL,
SAL INT NOT NULL
)
GO
INSERT INTO SAL(LNAME,FNAME,SAL)
select ‘WILLIAMS’,’JHON’,3600
union all
select ‘STONE’,’BILLY’,45000
union all
select ‘RAMSEY’,’PAULA’,75000
UNION ALL
SELECT ‘MURPHY’,’BERNARD’,10000
WITH NARDO
AS
(SELECT ID, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS NTHSALARY
FROM SAL)
SELECT ID, SAL, NTHSALARY
FROM NARDO WHERE NTHSALARY = 2
Thanks,
Dhirendra
I didn’t realize I made a slight boo-boo with respect to the insert statement. Sorry about that.
Display the 3rd highest salary. using this query we can find out Nth maximum value. in the 1st where clause put the number you want to display.
select salary from(select salary from(select salary from employee
order by salary desc)
where rownum<=3
order by salary)
where rownum=1;
its very simple to understand.
SELECT max(age) FROM Student
WHERE age < ( SELECT max(age) FROM Student
WHERE age < ( SELECT max(age) FROM Student
WHERE age < ( SELECT max(age) FROM Student )));
What does this query do? Finding thrid maximum? It will be slow for large tables. Refer alternate methods here
SecondHighest—
SELECT max(age)SecondHighestAge FROM e WHERE age < ( SELECT max(age) FROM e);
Hi,
You can do the same thing in good manner with CTE (Commom table expression)
with cte
as
(
select age,dense_rank()over(order by age desc) as row from e
)
select * from cte where row=2;
Thanks,
Dhirendra
How can I get the Nth highest salary in Sql server 2008
Refer these methods
The letter A appears after nested select statement. What does this do? Thanks.
It is a table alias for the subquery
I just need to know how to increase a salary by 10% using SQL? What command do I need to use…. Increase all employees’ salaries with the selected EEO-1 classification by 10%.
select empid,ename,esal,esal+(esal*10/100) as Increment_Salary from emp
For accuray use this
select empid,ename,esal,esal+(esal*10.0/100) as Increment_Salary from emp
For more informations refer this
This is simple
select salary+salary*0.10 from table
Hi Pinal,
I am Hitesh Shah (Software testing profile), I am new in this Blog , and I want simple query which I can understand and speak in interview.
How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table?
Thanks
Hitesh Shah
if uou are new to this blog then please go though all the post in this thread.
Hai,i thought it will help .you to find the any highest sal as per requirement.i face the question on interview.
Select * from tablename e1 where N=(select count(distinct (e2.sal))from tablename e2 where e2.sal>=e1.sal
ex-
select * from emp e1 where 3=(select count(distinct(e2.sal)) from emp e2 where e2.sal>=e1.sal)
this above query is used to find the 3 max salary of table
Hi guys.This is one of the interview question.
how to display emp salary and his manager salary using emp table.