FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to rank employees by age within department?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to rank employees by the application of ranking functions:

USE AdventureWorks;

 

SELECT *,

       RANK()

         OVER(PARTITION BY Department ORDER BY age) AS [Rank],

       MAX(age)

         OVER(PARTITION BY Department )   AS [Oldest in Department],

       MIN(age)

         OVER(PARTITION BY Department )   AS [Youngest in Department]

FROM   (SELECT Name = FirstName + ' ' + LastName,

               Department = d.Name,

               DATEDIFF(YEAR,birthdate,getdate()) AS Age

        FROM   HumanResources.Department d

               JOIN HumanResources.EmployeeDepartmentHistory edh

                 ON d.DepartmentID = edh.DepartmentID

                    AND EndDate is NULL

               JOIN HumanResources.Employee e

                 ON edh.EmployeeID = e.EmployeeID

               JOIN Person.Contact c

                 ON e.ContactID = c.ContactID) AS a;

/*

Name              Department        Age   Rank  Oldest      Youngest

Tengiz Kharati    Document Control  30    1     44          30

Sean Chai         Document Control  33    2     44          30

Chris Norred      Document Control  33    2     44          30

Karen Berge       Document Control  44    4     44          30

Zainal Arifin     Document Control  44    4     44          30

*/

 

Exam Prep 70-461
Exam 70-461