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 (http://blog.SQLAuthority.com), Pravin Phatangare
pinaldave
Hi Pinal,
myself Dharmendra, and am a Regular reader of ‘SQlAuthority’.
its a Good one Article about finding nth Higest Salary..
if am not wrong we can also do the same thing by using aggreate function like Min or Max..
Like if i want to know 4th Highest Salary then i can
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)
Regards
Dharmendra k Dixit
Works like a Charm!!
I think this question is one of the highest hits in interviews…
Thanks.
how can i get second hight salary or 3 rd hight salary
here
iam giving example
select top 1 quantity from(SELECT TOP n-1 quantity FROM (SELECT DISTINCT TOP n quantity FROM productorder ORDER BY quantity DESC) as A ORDER BY quantity desc )as B order by quantity
n->represt top nth records
n-1 is the which highest salary you want
@ Dharmendar
Bro.. your code is quite long and might be resource consuming because ,
1. your are selecting the minimum from a whole bunch of values.
2. You need to write Distinct in the query only then you will give the correct answer.
Like your code should be :
SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC)
HELLO !
THIS QUESTION IS DIFFICULT .
BUT I TRY THE ANSWER
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY DESC; ——–> (THIS IS USED FOR RETRIEVE THE MAXIMUM TOP 5 PAID EMPLOYEES )
OR
TO RETRIEVE THE MIN VALUE USE THIS
SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY;
THANK YOU.
Nice.
It really helps a lot.
Thanks.
To Imran..
Yes Bro ..Thanks for ur Suggestion..
it might be resource Consuming..
but i think so, its an alternative for finding nth Highest Salary..
looking forward ur openion..
Regards
Dharmendra k Dixit
hi there guys i need a query but i can´t get it
here i go the table has department_id, salary, employee_id
how can i increase the salary of all employees of a department in the min salary paid in that department
as example department 1 the min sal paid is 1000, i have to paid now to each employee his salary +1000
I think following query will give second highest salary. You can get nth highest by replacing 2 with n.
select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
Hi,
I am Praveen. I am visiting this site for first time. The above query is really good and generic. We can use this also to get nth lowest salary by just changing the order from DESC to ASC.
hi
i hv tried with this query but i am not able to get the exat answer
i tried with this query it works fine
===========================
put any digit instead of 5 u can get exat result
select distinct a.salary from Salary a where 5=( select count(distinct b.salary) from Salary b where a.salary<=b.salary)
==================================
ok friends tell me which query wil be faster
this query or using top n query
u can mail me at
dolagobinda.sahoo@gmail.com for my feed back
For SQL Server 2005 you have a Windowed function ROW_NUMBER, which you can use as follows:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (order by EMP.SAL DESC) AS RANK FROM EMP) v where RANK = 10;
In continuation to my above comment, I agree that this solution might not give exact result while SAL has duplicate values, but again one can play with the query a little bit. and get desired results.
I required sql query for select top 2 highest paid employee from each department
Hi friends,
select max(sal)from emp e1 where 2<=(select count(*) from
emp e2 where e1.sal <= e2.sal);
This query is ’selecting the n th highest salary’. This query is correct.
I want know how to run this query. please explain this sql query.
hai ,
iam just asked that i want to know the maximum salary and minum salary along with the employee details
Hi Prasad,
Hope this will work for you
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MIN(b.Salary)
FROM #T1 AS b)
UNION
SELECT a.ID1, a.[Name], a.Salary AS Salary
FROM #T1 AS a
WHERE a.Salary IN (
SELECT MAX(b.Salary)
FROM #T1 AS b)
ORDER BY a.Salary
DROP TABLE #T1
GO
The above query will also give you List of Employees who has Min Salary and List of employees having Max Highest Salary
Vamshi
For getting list of employees with Nth Highest Salary, Here is another approach, Probably this would be similar to one of the above queries
DECLARE @n INT — Variable for accepting N
SELECT @n = 2 — Get the Second Highest Salary
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
SELECT a.ID1, a.[Name], a.Salary
FROM #T1 AS a
WHERE (@n-1) = (
SELECT COUNT(DISTINCT(b.Salary))
FROM #T1 AS b
WHERE b.Salary > a.Salary)
DROP TABLE #T1
GO
Vamshi
This can also be done using DENSE_RANK() function of SQL Server 2005.
DECLARE @n INT — Variable for accepting N
SELECT @n = 2 — Get the Second Highest Salary
CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)
WITH T1 AS
(SELECT (DENSE_RANK() OVER (ORDER BY Salary DESC)) AS RNum, * FROM #T1)
SELECT a.ID1, a.[Name], a.Salary
FROM T1 AS a
WHERE a.RNum = (@n)
Vamshi
Hi guys,
My requirement is like this.
I need four data belonging to four latest audit dates for every business.
I used something like…
SELECT TotalUnitCount FROM @TotalUnitsInLot SecondLatestTotalUnits
WHERE (2-1) =
(SELECT COUNT(DISTINCT(LatestTotalUnits.AuditDate))
FROM @TotalUnitsInLot LatestTotalUnits
WHERE LatestTotalUnits.AuditDate > SecondLatestTotalUnits.AuditDate
AND LatestTotalUnits.BusinessId = SecondLatestTotalUnits.BusinessId
AND LatestTotalUnits.BusinessId = TotalUnits.BusinessId
GROUP BY BusinessId
)
This gives me the results I want, but this is really slow. The problem with
SELECT TOP 1 TotalUnitCount
FROM (
SELECT DISTINCT TOP 4 TotalUnitCount
FROM TotalUnitsInLot
ORDER BY TotalUnitCount DESC) a
ORDER BY TotalUnitCount
approach is, if there are only 3 audits for a business, this query still gets the data from 3rd audit and gives me those results. If there is no data for the 4th audit I want to show TotalUnitCount = 0 for the fourth audit. How do I do that ?
Give the desired Input and Output clearly in the form of a table. then it would be easy for anyone to answer your question.
Thanks…
my requirement is to get all the emp names ,their respective manager names in a single query..
like
empno emp_name Emp_mgr
101 xyz 105
102 abc 104
103 red 101
104 kfc 105
105 desa 118
here i need emp_name and his manager name..like
emp mgr
xyz desa
abc kfc
Hi Pavan
Below is the answer to your problem
Select a.emp_name,b.emp_name as manager from empman a, empman b where a.emp_mgr = b.empno
I hope this solves your problem
Hi,
One question in this query – what if there are multiple employees who has the nth highest salary? How can we get all the employees with the nth highest salary?
Thanks,
Kaushik.
I have about 50000 records inn a table sorted by MODEL_ID
I want to select the last 200 highest MODEL_ID in the table.
How would I do that?
BUSHIKSHIK
select min(sal) from (select top 200 sal from table order by sal desc) as a
Pls Help Me
Want SQL Query to retrieve records from two tables but the records which are same in both tables happen once.
hey raman contact me for other queries.
Hi All,
How to find a highest salary in employee table with emplooyee name.
plz help me on this
Hi,
I tried with this query but i am not satisfied.
could u please explain in detail.
This query gives highest salary for employee and employee name.
select empsalry,empname
from empsalr
where empsalry = (select max(empsalry)
from empsalr)
select empsalry,EMPNAME
from empsalr
where empsalry = (select max(empsalry)
from empsalr)
Please Help
I am trying to calculate the Median of DATEDIFFERENCE for each Episode. I have succeeded at obtaining the Difference in dates but am unable to extract the nthsmallest by episode.
My Query right now is
SELECT TOP 1 PERCENT DATEDIFF(dd, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE]) AS DIFFERENCE,
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE],
TABLE2.[Act START DATE] AS [NEXT Session], OPENCLOSE.OPENID
FROM dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE INNER JOIN
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE TABLE2 ON
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE] = OPENCLOSE.OPENDATE AND
dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO = OPENCLOSE.CASENO AND TABLE2.[Act START DATE] > OPENCLOSE.OPENDATE
GROUP BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE], dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO,
OPENCLOSE.OPENID
ORDER BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE]
Sorry if I confuse anyone I am new with SQL I handle Crystal Reports XI and minimal SQL. By the way I am running SQL Enterprise 2000.
Thank You
I want to know query to get highest paid salary for each department from Dept(Dept type,Id) and Employee table(Name,Emp no,..).
Aslo want to know query to list all employee from sales Dept from Employee and Dept Table
Question :
select the name of Manager who has max number of employees .
(Only the name of manager nothing else)
solution : ( I am posting my solution and hoping if someone has better one please respond ).
create table #mgr ( id int not null, name varchar(10) )
create table #emp ( id int not null, mgrID int, name varchar(10))
(note: mgrID is foreign key from #mgr )
insert into #mgr values (1, ‘mgr1′)
insert into #mgr values (2, ‘mgr2′)
insert into #mgr values (3, ‘mgr3′)
insert into #mgr values (4, ‘mgr4′)
insert into #emp values (1, 1, ‘emp1′)
insert into #emp values (2, 1, ‘emp2′)
insert into #emp values (3, 1, ‘emp3′)
insert into #emp values (4, 1, ‘emp4′)
insert into #emp values (5, 1, ‘emp5′)
insert into #emp values (6, 1, ‘emp6′)
insert into #emp values (7, 1, ‘emp7′)
insert into #emp values (8, 2, ‘emp8′)
insert into #emp values (9, 2, ‘emp9′)
insert into #emp values (10, 2, ‘emp10′)
insert into #emp values (11, 2, ‘emp11′)
insert into #emp values (12, 2, ‘emp12′)
insert into #emp values (13, 2, ‘emp13′)
insert into #emp values (14, 3, ‘emp14′)
insert into #emp values (15, 3, ‘emp15′)
insert into #emp values (16, 3, ‘emp16′)
insert into #emp values (17, 3, ‘emp17′)
insert into #emp values (18, 4, ‘emp18′)
insert into #emp values (19, 4, ‘emp19′)
insert into #emp values (20, 4, ‘emp20′)
select m.name from #mgr m
join
(
select m.id, count(m.id) as cnt
From #mgr m
join #emp e
on m.id = e.mgrID
group by m.id
) x
on m.id = x.id
Where x.cnt =
(
select max(cnt) from
( select m.id, count(m.id) as cnt
From #mgr m
join #emp e
on m.id = e.mgrID
group by m.id
) y
)
how can we get the highest,2nd highest and 3rd highest salary from the employee table along with the employee names in sql
Q)How can we get the highest,2nd highest and 3rd highest salary from the employee table along with the employee names in sql ?
Ans:-
select Emp_Sal,Emp_Name from employee where Emp_Sal in (select top 3 Emp_Sal from employee order by Emp_Sal desc);
In the salary senario , you must also take care that salaries can be duplicate in a table , the easiest way to get nth heighest salary is
select top 1 salary from
(select top n salary from employee e1 group by e1.salary order by e1.salary desc) e2
order by salary
Its real simple. Suppose your employees are in a table called by the same name, and the salaries in a column named salaries). Lets See te case in which we want the 3 greatest salaries. Then you Query:
SELECT employees.salary
FROM employees
ORDER BY employees.salary desc
LIMIT 3;
If you want the biggest 5 salaries, you put LIMIT 5, and so on….
@Maximiliano Grass
LIMIT is not ANSI SQL keyword, not untill SQL Server 2005. I tried checking in SQL Server 2008, I could not see it online.
What I found on internet is LIMIT is a key word used in MYSQL. ( I mean only in MYSQL) no other RDBMS uses it.
Can you provide more info about this key word. Looks cool.
Thanks,
IM.
If two records are there in the table in 3rd position then how to calculate this???
thanks pinkal … really great work …
Hi Pinal Dave,
i use to read blog past one year.
#1
i have the doubt on the finding nth maxi salary.
every one has their own method even myself too.
but which one is good performance ?
i tried to get clear from execution plan but still struggling to find which one is? can you please ?
#2 how to find the execution performance of a query by looking it.
just is there any execution time consideration for operators like union , joins etc
for example: the operator precedence in languages
can anyone tell me what is the meaning of a in the following query and what is the use of it
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
@ Prem.
It Shows the Sixth Maximum Salary.
~ IM.
thanks imran,
that much i know but i want to know about the meaning and use of a in the second last line
@ Prem,
Its is nothing but just a subquery.
If subquery is followed by a FROM clause, SQL Server treat this sub query as a table.
And in your script, the name of this table (formed by subquery) is a.
~ IM.
can anyone tell me what is the meaning of a in the following query and what is the use of it
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
” a “–>wat it means
@ramu
It is aliasing the TABLE in the FROM clause.
See the help for the FROM clause for more details.
http://msdn.microsoft.com/en-us/library/ms177634.aspx
hi pinal sir,
select top 1
from
(select top 3 salary
from emp35
order by salary desc) a
order by salary
i am not getting result for this query in sqlserver2000 and that to giving error like…i amnt specifyinf attribute name at first line why bcoz i want entire record(i dont want to show single column)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘from’.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword ‘order’.
@ramu
The first SELECT does not specify a COLUMN:
select top 1
salary
from
(select top 3 salary
from emp35
order by salary desc) a
order by salary
hi brain…..thank u for responding but ………i want to get the first record………..how can i by using that query(which is already posted)
@ramu
The DESC should be supplied in the outer query too.
The first SELECT does not specify a COLUMN:
select top 1
salary
from
(select top 3 salary
from emp35
order by salary desc) a
order by salary desc
hi pinal,
\12353-435764.tif.tif in one column but iwant same thing in another which is existing column \12353-435764.pdf.
i want only .pdf extension only that to only one time
if i tis like this i done like
update tbl
set col2=replace(col1,’.tif’,’.pdf’)
\12353-435764.tif
but i dont know wen it is in \12353-435764.tif.tif
how to convert into
\12353-435764.tif.tif–>\12353-435764.pdf
i amnt able to do like this by using patterns