How to get the second largest salary of the accounts department?

Question

I have three tables as shown below:

Create table #temp (id int, DepartmentName varchar(50)) insert into #temp (id,DepartmentName) values(1,'Account') insert into #temp (id,DepartmentName) values(2,'IT') select * from #temp Create Table #temp1(customerid int, CustomerName varchar(50),DepartmentId int) Insert into #temp1(customerid,CustomerName,DepartmentId)values(1,'Anil',1) Insert into #temp1(customerid,CustomerName,DepartmentId)values(2,'Ankit',2) Insert into #temp1(customerid,CustomerName,DepartmentId)values(3,'Mandeep',1) Insert into #temp1(customerid,CustomerName,DepartmentId)values(4,'Rajesh',2) Insert into #temp1(customerid,CustomerName,DepartmentId)values(5,'Rohit',1) Insert into #temp1(customerid,CustomerName,DepartmentId)values(6,'Sharma',0) Create Table #temp2(customerid int, salary int) insert into #temp2(customerid,salary)values(1,2000) insert into #temp2(customerid,salary)values(3,2399) insert into #temp2(customerid,salary)values(4,4000) insert into #temp2(customerid,salary)values(2,4500) insert into #temp2(customerid,salary)values(5,7000) select max(t2.salary) ,t.CustomerName,t1.DepartmentName from #temp1 t left join #temp t1 on t1.id=t.DepartmentId left join #temp2 t2 on t2.customerid=t.customerid where DepartmentName='Account' and salary<>(select max(tt2.salary) from #temp2 tt2 inner join #temp1 tt1 on tt1.customerid=tt2.customerid inner join #temp tt on tt.id=tt1.DepartmentId where tt.DepartmentName='Account') group by CustomerName,DepartmentName 

But not having received the second largest salary with this, someone may like to help me with this, I use the maximum aggregate function in the external request, but still get the whole salary of the account department.

Answer 1

Use Dense_Rank

 SELECT DepartmentName, CustomerName, salary FROM ( SELECT t.DepartmentName, DENSE_RANK() OVER( PARTITION BY id ORDER BY salary desc) rno, salary, t1.CustomerName FROM #temp t JOIN #temp1 t1 ON t.id = t1.DepartmentId JOIN #temp2 t2 ON t1.customerid = t2.customerid where t.DepartmentName='Account' ) a WHERE rno = 2 

Update Modify your request using Dense_Rank , because we are unable to complete the group with customer names.

 SELECT salary, CustomerName, DepartmentName FROM (SELECT t2.salary, t.CustomerName, t1.DepartmentName, Dense_rank() OVER( partition BY DepartmentName ORDER BY salary DESC) rno FROM #temp1 t LEFT JOIN #temp t1 ON t1.id = t.DepartmentId LEFT JOIN #temp2 t2 ON t2.customerid = t.customerid WHERE DepartmentName = 'Account' AND salary NOT IN (SELECT Max(tt2.salary) FROM #temp2 tt2 INNER JOIN #temp1 tt1 ON tt1.customerid = tt2.customerid INNER JOIN #temp tt ON tt.id = tt1.DepartmentId WHERE tt.DepartmentName = 'Account'))a WHERE rno = 1 

Answer 2

Try the following:

  SELECT #temp1.customerid, CustomerName, DepartmentName, Max(salary) FROM #temp JOIN #temp1 ON id = DepartmentId JOIN #temp2 ON #temp1.customerid = #temp2.customerid WHERE id = 1 AND salary NOT IN (SELECT Max(salary) FROM #temp2) GROUP BY #temp1.customerid, CustomerName, DepartmentName 

Answer 3

This is the easiest and fastest way to do this. Better than using rank ...

 select top 1 t1.Customerid, t1.CustomerName, t2.Salary, t.DepartmentName from #temp1 t1 Join #temp2 t2 on t2.customerId = t1.Customerid Join #temp t on t.id = t1.departmentid and t.departmentName = 'account' where t2.salary < ( select MAX(t2.salary) from #temp1 t1 Join #temp2 t2 on t2.customerId = t1.Customerid Join #temp t on t.id = t1.departmentid and t.departmentName = 'account' ) order by t2.salary desc 

Answer 4

 ;WITH cte AS( SELECT t1.customerid,t1.CustomerName,t2.DepartmentName FROM #temp1 t1 LEFT JOIN #temp t2 ON t1.DepartmentId = t2.id) ,cte2 as( SELECT t3.*,t4.salary,ROW_NUMBER() OVER(ORDER BY t4.salary DESC) AS rno FROM cte t3 LEFT JOIN #temp2 t4 ON t3.customerid = t4.customerid) SELECT * FROM cte2 WHERE rno=2 

Answer 5

I am also trying :)

 with cte as ( select c.CustomerName, d.DepartmentName, s.Salary, RowNumber = row_number() over (partition by c.DepartmentId order by s.Salary) from #temp1 c inner join #temp d on d.Id = c.DepartmentId inner join #temp2 s on s.CustomerId = c.CustomerId ) select CustomerName, DepartmentName, Salary from cte where RowNumber = 2 and DepartmentName='Account'; 

Edited : dance_rank is better here. Buddha gave his answer earlier!

Answer 6

 select t1.customerid,t2.salary,t1.CustomerName,t.DepartmentName from #temp2 t2 join #temp1 t1 on t2.customerid=t1.customerid join #temp t on t1.DepartmentId =t.id where salary=( select MIN(salary) as salary from (select distinct top(2) salary from #temp2 order by salary desc )T) 

Try to execute code