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
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
ashish how to find out Avg sal of all employees means like
manager1 contains 4 employees
manager2 contains 10 employes
manager 3 contains 6 emps…ok
my question is under manager 1 avg sal of 4 emps and mgr 2 avg sal of emp’s….
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
There are many methods availble
Refer this for more informations
For a table created using the following commands. State the SQL SELECT command to find the 1st and 2nd highest salary earned by staff working in DEL or MUM
CREATE TABLE Employees (
ID int NOT NULL AUTO_INCREMENT,
Name varchar(100),
Sal decimal (10,2),
City char (3),
PRIMARY KEY (ID)
);
INSERT INTO Employees (Name, Sal, City) VALUES (‘Ramesh’,20000, ‘BLR’);
INSERT INTO Employees (Name, Sal, City) VALUES (‘Sunil’,24000, ‘DEL’);
INSERT INTO Employees (Name, Sal, City) VALUES (‘Sreeja’,21000, ‘MUM’);
INSERT INTO Employees (Name, Sal, City) VALUES (‘Pavan’,23000, ‘DEL’);
INSERT INTO Employees (Name, Sal, City) VALUES (‘Maya’,24000, ‘MUM’);
Please reply soon
hi kumari
this is anurag
use sub query
select name,sal from emp where sal=(select max(sal) from emp);
select emp_name,salary from salary_tab where salary=(select max(salary) from salary_tab)
Hi,
I tried with this query but i am not satisfied.
could u please explain in detail.
Hi
This query gives complete details of employee who are getiting max/min salary
select * from emp where salary=(select max(salary) from emp);
select * from emp where salary=(select min(salary) from emp);
(note: here table name emp,salary is coumn name)
thank u …..
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….