Second highest salary - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Second highest salary

Author

Topic

kawadeprasad
Starting Member

13 Posts

Posted - 2012-05-16 : 13:01:12

I have different departments, I would like to get second highest salary from each department.
Please help.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-16 : 13:06:10

What have you tried so far? Please post your table structures, some sample data, and expected results.

Do you want just the salary? Or also the people receiving it? What if two or more people receive the highest or second highest salaries? Which salary is 2nd?

kawadeprasad
Starting Member

13 Posts

Posted - 2012-05-16 : 13:16:29

I just want 2nd highest salary. with max function and group by we can get highest but i need 2 highest.

RL
Starting Member

15 Posts

Posted - 2012-05-16 : 15:02:23

Here's what I did using the following table definition and data, assuming you want the employee name and it's ok to list all employees who have the second highest salary:


CREATE TABLE dept_salaries
(dept_name VARCHAR(50),
 emp_name VARCHAR(50), 
 salary INT);
GO

INSERT dept_salaries
VALUES ('Sales', 'Smith',  100),
       ('IT',    'Jones',  500),
       ('IT',    'Green',  150),
       ('Sales', 'Olsen',  300),
       ('IT',    'Gates',  400),
       ('Sales', 'Fields', 200),
       ('IT',    'Lowe',   250),
       ('Sales', 'Mayes',  200);
GO

;WITH CTE 
AS
(
  -- max salary by department
  SELECT dept_name, MAX(salary) max_salary
    FROM dept_salaries D1 
   WHERE salary < (SELECT MAX(salary)
                     FROM dept_salaries
                    WHERE dept_name = D1.dept_name)
  GROUP BY dept_name
)
-- list employees with the salary in CTE
SELECT D1.dept_name, D1.emp_name, D1.salary
  FROM dept_salaries D1 JOIN CTE D2
       ON D1.dept_name = D2.dept_name
          AND D1.salary = D2.max_salary
ORDER BY 1, 2;

/* Results
IT    Gates  400
Sales Fields 200
Sales Mayes  200
*/