Find the employee with the third largest salary
CREATE TABLE Employees
( empid int,
ename varchar(50),
salary int
);
insert into Employee values (1, 'emp1', 1000);
insert into Employee values (2, 'emp2', 1001);
insert into Employee values (3, 'emp3', 1002);
insert into Employee values (4, 'emp4', 1003);
insert into Employee values (5, 'emp5', 1000);
insert into Employee values (6, 'emp6', 1001);
insert into Employee values (7, 'emp7', 1003);
Question:
Find the employee with the third largest salary in the organization
[609 byte] By [
skdp] at [2007-11-18 18:05:06]
# 4 Re: Find the employee with the third largest salary
Dear hspc,
Your solution is just for one fix problem but what happen if my criteria change e.g. now my requirement changes from third largest to fifth largest salary then your solution will fails and for achieving my problem solution either i will create a new SQL Query or update existing SQL Query.
For taking care of above scenerio i have made following generic solution:
CREATE PROCEDURE GetNthLargestSalary
@NthPosition INT
AS
SELECT * FROM Employees A
WHERE @NthPosition = (SELECT COUNT(DISTINCT salary) FROM EMPLOYEES B
WHERE B.salary >= A.salary)
GO
Execute above Stored procedure by passing position number i.e. if you want third largest salary then pass 3 and if you want fifth largest salary then pass 5 and you will get the result what you want.
ITGURU at 2007-11-9 13:40:25 >
