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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
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