Finding the first and 4th salary

Transact-SQL
1

HI guys , a novice question, what s the best, simple way to find the 2nd,third, and 4th highest salary?
Thanks!
Pasi.

CREATE TABLE Employee (name varchar(10), salary int);

-- inserting sample data into Employee table
INSERT INTO Employee VALUES ('Rick', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Shane', 3000);
INSERT INTO Employee VALUES ('Peter', 5000);
INSERT INTO Employee VALUES ('Jackob', 7000);

2

SELECT Salary,name
FROM
(
SELECT Salary,name ,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
FROM EMPLOYEE
) As A
WHERE A.RowNum IN (2,3,4)

3

Thanks Ahmed, I will try this.
Pasi

4

hi

i think there desc should be added
ROW_NUMBER() OVER(ORDER BY Salary )
should be
ROW_NUMBER() OVER(ORDER BY Salary desc)

SELECT Salary,name
FROM
(
SELECT Salary,name ,ROW_NUMBER() OVER(ORDER BY Salary desc) As RowNum
FROM EMPLOYEE
) As A
WHERE A.RowNum IN (2,3,4)

1 Like
5

Depending on your business rules, you may want to use ROW_NUMBER function or DENSE_RANK function. For example, if there are two employees who have the same salary and that salary happens to be the 2nd (or 3rd or 4th) highest, choice of which function you use becomes important.

1 Like