DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to obtain the maximum salary for each deparment?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to find the maximum salaried employee at each deparment.

-- MSSQL highest salary by department - maximum salary
-- Microsoft T-SQL inner join - correlated subquery
USE AdventureWorks;
SELECT Highest = '$'+convert(varchar,eph.Rate,1),
       Staff = 'Highest salaried employee in ' + d.name + ' is ' +
                c.LastName + ', ' + c.FirstName
FROM   HumanResources.Department d
       INNER JOIN HumanResources.EmployeeDepartmentHistory edhmain
         ON d.DepartmentID = edhmain.DepartmentID
       INNER JOIN HumanResources.EmployeePayHistory eph
         ON eph.EmployeeID = edhmain.EmployeeID
       INNER JOIN HumanResources.Employee emain
         ON edhmain.EmployeeID = emain.EmployeeID
       INNER JOIN Person.Contact c
         ON emain.ContactID = c.ContactID
WHERE  eph.Rate = (SELECT MAX(Rate)
                   FROM   HumanResources.EmployeePayHistory eph
                          INNER JOIN HumanResources.Employee e
                            ON eph.EmployeeID = e.EmployeeID
                          INNER JOIN HumanResources.EmployeeDepartmentHistory edh
                            ON edh.EmployeeID = e.EmployeeID
                   WHERE  edh.DepartmentID = edhmain.DepartmentID
                     AND  edh.EndDate is null)
 AND  edhmain.EndDate is null
 ORDER BY eph.Rate DESC;
GO
 
/* Results
 
Highest     Staff
$125.50     Highest salaried employee in Executive is Sánchez, Ken
$84.13      Highest salaried employee in Production is Hamilton, James
$72.12      Highest salaried employee in Sales is Welcker, Brian
$63.46      Highest salaried employee in Engineering is Duffy, Terri
$50.48      Highest salaried employee in Research and Development is Miller, Dylan
$50.48      Highest salaried employee in Information Services is Trenary, Jean
$43.27      Highest salaried employee in Finance is Kahn, Wendy
$37.50      Highest salaried employee in Marketing is Bradley, David
$30.00      Highest salaried employee in Purchasing is Word, Sheela
$29.85      Highest salaried employee in Tool Design is Walters, Rob
$28.85      Highest salaried employee in Quality Assurance is Abolrous, Hazem
$27.14      Highest salaried employee in Human Resources is Barreto de Mattos, Paula
$24.52      Highest salaried employee in Production Control is Krebs, Peter
$24.04      Highest salaried employee in Facilities and Maintenance is Altman, Gary
$19.23      Highest salaried employee in Shipping and Receiving is Ackerman, Pilar
$17.79      Highest salaried employee in Document Control is Arifin, Zainal

*/
------------
 
 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE