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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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