Selecting Maximum Value with other column of other tables in SQL

Selecting Maximum Value with other column of other tables  in SQL

you might have faces a situation where you want maximum value a column with other columns from other tables also.
you can use group by in order to get Maximum value but if you are displaying all fields in the select query and joining with other tables  then you have to alter your query
.
Let me explain you through  a table

Suppose in a table you have empid name and salary the salary is revised again and new rows were added in the table with new salary you have one more table Projects with empid, projectid and projectname.
Now you have to display Empid,Name Porjectid,ProjectName and New Updated salary in a single query
Like below screen shot.

we will create to tables

create table Employee1_error3

(Empid int ,EmpName nvarchar(50),EmpSalary float)

insert into Employee1_error3 values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

--salary revised or updated

insert into Employee1_error3 values(1,'Amit','15000'),(2,'Sumit','16000'),(3,'Raj','18000'),(4,'vijay','19000'),(5,'suresh','110000')

select * from Employee1_error3

now you have another table 

Create table Projects1

(Empid int,Projectid int ,ProjectName varchar(50))

insert into Projects1 values(1,100,'Test1'),(2,101,'Test12'),(3,102,'Test13'),(4,103,'Test14'),(5,104,'Test15')

Now in your query you want new empid ,empname, salary with projectid and projectnames 

i will tell the simple and easy way

select a.*,b.projectid,projectname from 

(select empid,empname, max(EmpSalary) as salary  from Employee1_error3

group by empid , empname)A

join 

(select empid, Projectid,ProjectName from Projects1)B 

on B.empid=A.empid

Another way using cte and row num

with cte as 

(

select A.empid,empname,Projectid,ProjectName ,empsalary,row_number() over(partition by A.empid order by empsalary desc) as Rownum from Employee1_error3 A 

join Projects1 B on B.empid=A.empid

)

select * from cte where rownum=1

 In the Second query rownum is just for reference you can remove * and write columns name to get exact result in outer part of CTE.

Note : There are other ways also to achieve this results but i found the above ways easy and simpler.