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
i have a question in sql,any one please help me to get out from here.
Q.Table name employee in which we have 200 emp list.i want to display the name of the employee who have highest salary from 200 employee table data.
Please help me to get the answer.
Hi harish,
You can use this Query:
Select * from table where sal = (select max(sal) from table)
Seriously I want to hug whoever posted this answer originally. THREE DAYS of trying to figure out this code and now it WORKS !!!
THANK YOU THANK YOU THANK YOU !
SELECT TOP 1 Invoicetestdate1.[TestDate] AS Expr1
FROM (SELECT DISTINCT TOP 2 Invoicetestdate1.[TestDate]
FROM Invoicetestdate1
GROUP BY Invoicetestdate1.[TestDate]
ORDER BY Invoicetestdate1.[TestDate] DESC) AS a
ORDER BY Invoicetestdate1.[TestDate];
invoicetestdate1 is another query based on a filter generated by a form. But this solution works GREAT !
thanks anurag.it solved my problem.
here is another q i want to share.
q.how to do replication in sql server2005 with examle.
waiting…..
SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS ‘Salaries’
FROM #Employees
) emp
WHERE Salaries = n
n may be 2,3….
pls. help how to increase the current salary by 15%?using sql codes….tnx
update empregistration set salary= salary * 1.15….I hope this will help you….enjoy…..
That is definetely something I would like to know too! :D
But seriously, just add it with 1.15 like: SELECT Salary * 1.15 as RaisedSalary
@rona
UPDATE employee SET Salary = Salary * 1.15;
select * from emp e1 where 2=(select count(*) from
emp e2 where e2.id > e1.id);
id values – 1,2,3,4,5
Here i am getting third highest value
can any one explain me this query. here what is the use of value 2. and how we are getting third highest value i.e id 3.
Regards,
Rock..
i dont understand what your query displays
give brief explanation
I want to display every nth records using the select query in sql server . How can i achieve the same.
Hello Sunil,
Use the ROW_NUMBER fucntion to get a identity value for each row and then get the Nth row using modulo operator.
Regards,
Pinal Dave
thanx dear u made my day greate work … you solved my problem!!!!!
select * from(
select dense_rank() over (order by id)as rownum,* from tbl1
)temp where rownum=2
Hi…
How to get the highest rank holders list Without using subquery…
For example,
S1 80
s2 80
s3 79
s4 78
s5 78
s6 60
s7 70
I need to get Top 3 rankers from the list
Tht is
s1
s2
s3
s4
s5
How to get it without using subquery?
Thnks in Advance……
Hello Rocky,
Using DENSE_RANK() function in CTE you can get a raking value and retreive only the records where this ranking is less than 4.
For details see the following page:
https://docs.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql?view=sql-server-2017
Regards,
Pinal Dave
Instead of this you should use.
select Income from (
select Rank() over(order by Income desc) topRecord, Income
from (select distinct Income from tblName) tbl ) tbl2
where topRecord = 2
I am rahul Upadhyay This can be done without using TOP
SELECT *
FROM ( SELECT ROWNUM AS rank, Emp_id, Emp_salary
FROM employees
ORDER BY Emp_salary
)
WHERE rank=3 ;
i want to display 5 th highest salary from emp
complete row information(all columns of emp table who are getting 5 th highest salary)
select top 1 * from
(
select top 5 * from emp order by salary desc
) as t
order by salary
or
select * from
(
select row_number() over (order by salary desc) as sno,* from table)
as t
where sno=5
ROWNUM is for ORACLE
You need to use row_number() over (order by salary desc) in SQL Server
what is ‘a’ in the query
plz tell me.
a is alias.
alias name…….
Thank you Priyanka. I also had the same doubt.
a very simple way to get nth highest .. . i was using a complicated one :)
if you want to display highest salary from emp table
(display all emp table columns)
query is
select * from emp where salary=(select max(salary) from emp);
here sub query is used
above query displays complete information available on emp table who are getting highest salary
Other method is
select top 1 * from emp order by salary desc
–First Method
select top 1 salary from (
select top 6 salary from employee group by salary order by salary desc) a order by salary
go
–Second method
with nthsalary as
(
select salary,row_number() over(order by salary desc) as row from employee group by salary
)
select salary from nthsalary where row=6
GO
–Thired method
select distinct salary from employee a where 6=(select count(distinct salary)
from employee b where a.salary<=b.salary)
Top 3 Salaried Employee from Every Departments
create table #empdet(
empno int identity(1,1),
empname varchar(50),
sal numeric(18,2),
dep varchar(20)
)
go
insert into #empdet(empname, sal, dep)
select ‘A_123′,12000,’BANK’
Union
select ‘A_234′,5000,’BANK’
Union
select ‘A_345′,10000,’BANK’
Union
select ‘A_456′,25000,’BANK’
Union
select ‘A_567′,8000,’BANK’
Union
select ‘B_123′,25000,’PROG’
Union
select ‘B_234′,27000,’PROG’
Union
select ‘B_345′,23000,’PROG’
Union
select ‘B_456′,13000,’PROG’
Union
select ‘B_567′,50000,’PROG’
Union
select ‘C_123′,11000,’TEST’
Union
select ‘C_234′,9000,’TEST’
Union
select ‘C_345′,22000,’TEST’
Union
select ‘C_456′,30000,’TEST’
Union
select ‘C_567′,8000,’TEST’
Select * from #empdet
Select * from #empdet a where empno in (select top 3 empno from #empdet where dep=a.dep order by sal desc)
There are many methods availble to find this
Refer this for more informations
hey Iam dinesh
I want to Add 3000 salary of tose employee where salary>20000
It is as simple as
update table
set salary=salary+3000
where salary>20000
select top 1 salary from(select distinct top 5 salary from student11 order by salary desc) a order by salary