 Hello friends. Welcome to SSUnitech. My name is Susheel Singh and this is continuation of SQL Server interview questions and answers. So in this session we will understand how we can get top to maximum salary with employees detail in each department. Let's assume you have more than one departments in your organization. So first is IT department and second is HR department. So we want to get those employees which salary is maximum in his department or second maximum. So let's move to another slide where we will understand what it means and how we can calculate. So here we can see we have two input tables. First is related to employee information and second is related to department information. In employee information we can see we have four input columns. First is employee ID, second is name, then salary and then department ID. And in department information table we have only two columns. First is department ID and second is department name. We want to get our output where we want four columns. First is ID, second name, then salary and then department name. As we can see here department name is available in department table and rest of the information ID, name and salary is available in employee information. Here we can see in department ID 1 we have department name is IT. So in IT department we have three employees. First is John, second Sunil and then Subhangi and we can see John's salary 40,000, Sunil's salary 40,000 and Subhangi's salary is 45,000. So as we can see Subhangi has the maximum salary in IT department. So we want to display Subhangi's record as in output. So in Sunil and John we can see these two has the same number of salary. So these two has 40,000 and 40,000. We can see they are getting second highest salary. So we have to display all these two employees in our output. As we can see here we have displayed John and then Sunil. Let's move to another department. Department ID 2 which is HR department. So in HR department we have three employees Pankaj, Mary and Deepak. So Pankaj's salary is 80,000 and Mary's salary is 55,000 and Deepak's salary is 75,000. So we can see Pankaj is getting the maximum number of salary in HR department. So his salary is 80,000. So we want to display in our output as it is. And then between Mary and Deepak Deepak is getting 75,000. So we have to display Deepak in our output. So this is our output. So how we can calculate this output by using SQL Server. So let's move to SQL Server Management Studio where we will understand how we can calculate. So as I am using SQL 2014, I have already created these two tables which is employee and department. I am going to execute. So here we can see this is our employee ID, employee name, salary and department and it has the six roles. In our slide we can see we have four columns and they have six roles. So all the metadata which is available in slide is same as we have inserted in our table. So if you need this script it will, our level on my Facebook page you can copy from there. So now I am going to use, first of all you can see in our slide we have four columns in our output. First is ID, then name, then salary and then department name. So we have to put inner join between department and employee table to get the department name. So first of all I am going to put inner join where we will get the department name. So select and then employee table. So this is employee table. I am going to put alias. It is then inner join with department table. So this is our department table and I am going to put alias is D and we are going to put join on the behalf of department ID. So this is our D dot department ID is equals to E dot department ID where this ID will be equal. Then we want to display our output. So in output we want to display employee ID, then employee name, then employee salary. So all these information is available in our employee table and we want to display department name instead of department ID. So department name is available in department table. So we can calculate from department table. Now I am going to execute and we can see all the columns is available as we have displayed in slide. It has employee ID, name, salary and department name. So here we can see employee ID, employee name, salary and department name. So all the columns are available over here. So now I am going to calculate the maximum and second maximum salary in each department. We have to apply dense rank by which we can assign some numbers and then we can simply filter on these numbers. So how we can apply dense rank? We have to put dense rank. So this is our dense rank. Then we have to use over clause. This is our syntax for dense rank. Order by clause is mandatory. So we have to order by on the behalf of salary and then we want to put in decreasing order. And now I am going to execute and we can see what would be the output. So this is the output. So Pankaj has the maximum salary in whole table so it comes first. Then Deepak has the second highest salary so it is over second. Mary has the third highest salary so it is over third. So Bhangi has the fourth then it is fourth. And then John and Sunil has the same number of salary which is in fifth position. But we don't want to calculate in this way. We want to partition by in department. So in department we can see we have to apply partition by on the behalf of department ID. So before order by we have to use partition by. So this is our partition by partition by on the behalf of department ID. So department ID is available in these two tables. So we have to put alias name by which we can see it will not reflect any error. Now I am going to execute and we can see it's going to partition on the behalf of department ID. So this is our one partition which is department ID is one and then department ID is two. Then it's going to again partition. So we can see we have one and two. So first is for the maximum salary second is for second highest salary. And in HR department we are going to get one, two and three. So 80,000 is the maximum salary then Deepak has the second highest salary and then Mary has the third highest salary. In the HR department but we want to display only these two records. So we can simply put filter and we can get the result. So now I'm going to use a city and within city it will be calculated. So city temp and then as then we can start a bracket and then we have to close the bracket. And here we can see we are going to select all the records from this city table. And now we can see we will get the same output as we have displayed. But we don't want to those records which RW value is not equals to one or two. So we want to pick only one and two. So now we can see we are going to get as we want in our output. We don't want RW so we have to assign the column name which is employee ID. Employee ID then employee name. Then salary then department name. So this is our department name. Now we can execute and we can see we are going to get 5 rows and 4 columns. So here we have 5 rows and 4 columns ID 3 1 2 4 and 6 here 3 1 2 4 and 6. So we can see we are going to get result as we have displayed in our slide. So I hope you have understand how we can calculate. If you like this video please subscribe our channel and share the video as well. If you need this script it will be available on my Facebook page you can copy from there. Thank you so much for watching.