Sql server-Useful queries

select *,max(sal) over (partition by DepartmentCode ) FROM emp  Result:

EmployeeNumber	FirstName	LastName	DepartmentCode	sal	max_sal	(No column name)
268-046	Martine	Nyamoto	ACNT	100	NULL	100
382-505	Paula	Waters	HMRS	100	NULL	300
773-148	James	Larsen	RSDV	100	NULL	300
495-728	Robert	Simms	RSDV	200	NULL	300
958-057	Peter	Aut	HMRS	200	NULL	300
279-242	Ann	Welch	HMRS	200	NULL	300
572-384	Jeannette	Welch	HMRS	300	NULL	300
283-947	Timothy	White	RSDV	300	NULL	300