SQL Server Query to find Highest or Maximum Salary in each Department

SQL Query to Retrieve Highest or Maximum Salary drawing Employee Details from each Department

Example :
Suppose we have a Employee Table as follows:

EMP Table :

EMP_ID	F_Name	L_Name	Dept_ID	Cur_Address	Perm_Address	Salary
1001	Ravi	Kumar	1	Hyderabad	Hyderabad	20000
1002	David	Smith	2	Bangalore	Pune	35000
1003	Victory	Venkatesh	1	Hyderabad	Hyderabad	50000
1004	Tamatam	Reddy	3	Bangalore	Hyderabad	25000
1005	William	Smith	2	Hyderabad	Bangalore	40000
1006	King	Fisher	6	Bangalore	Bangalore	30000

Now if we want to Retrieve Highest or Maximum Salary drawing Employee Details of each Department , from the above table , we can use the following SQL Sub Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name,Dept_Id,Salary From EMP Where Salary IN (SELECT MAX(Salary)AS Max_Sal From EMP GROUP BY Dept_Id)

Output :

E_Id	E_Name	Dept_Id	Salary
1003	Victory_Venkatesh	1	50000
1004	Tamatam_Reddy	3	25000
1005	William_Smith	2	40000
1006	King_Fisher	6	30000

#--------------------------------------------------------------Thanks--------------------------------------------------------------#

SQL SERVER CARTESIAN JOIN or CROSS JOIN Syntax and Example

SQL Server CARTESIAN Product or CROSS JOIN 
The Cross Join returns the Cartesian product of the sets of records from the two or more joined tables.
when a join condition is omitted when getting result from two tables then that kind of query gives us Cartesian product, in which all combination of rows displayed. All rows in the first table is joined to all rows of second table.

Syntax :

Select* from Table_I , Table_2....

OR

Select* from  T1.Column1,T1.Column2, T2.Column3,T2.Column4 
from Table_I  T1, Table_2 T2

Example :

Suppose we have a Two Tables as follows:
EMP Table :

DEPT Table :

Cartesian or Cross Join Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name , D.Dept_Id,D.Dept_Name 
from EMP E, DEPT D

Output :

As the Table_1(EMP) contains 6 records and Table_2(DEPT) has 3 records , the Cartesian/Cross Join gives an output of 18 (6*3) records as shown below :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------