Find the Three Longest Employed by Department
By Kalman Toth, M.Phil., M.Phil., MCDBA

May 21, 2005

Frequently an SQL developer or administrator is requested to produce statistical reports by department. An interesting instance of such reports is the “top x by department” or the “bottom x by department”. The following query searches for the three longest employed by department. To change it over for the 3 shortest employed, the “order by HireDate” has to be changed to “order by HireDate desc” in the correlated subquery.

use AdventureWorks
select d.Name, c.FirstName+' '+c.LastName, convert(char(10),e.HireDate,101)
from HumanResources.Employee e, Person.Contact c, HumanResources.Department d
where e.HireDate in
   (select top 3 HireDate
   from HumanResources.Employee
   where DepartmentID = e.DepartmentID
   order by HireDate )
   and e.DepartmentID=d.DepartmentID
   and e.ContactID=c.ContactID
order by d.name, HireDate

Here is the output:

Department Employee Start Date

Document Control Tengiz Kharatishvili 01/17/2002
Document Control Zainal Arifin 02/05/2002
Document Control Sean Chai 02/23/2002
Engineering Roberto Tamburello 12/12/2000
Engineering Gail Erickson 02/06/2001
Engineering Jossef Goldberg 02/24/2001
Executive Ken Sánchez 02/15/2002
Executive Laura Norman 03/04/2002
Facilities and Maintenance Christian Kleinerman 01/15/2002
Facilities and Maintenance Gary Altman 01/03/2003
Facilities and Maintenance Magnus Hedlund 01/22/2003
Finance Deborah Poe 01/19/2002
Finance Wendy Kahn 01/26/2002
Finance Candy Spoon 02/07/2002
Human Resources Paula Barreto de Mattos 01/07/2002
Human Resources Willis Johnson 01/14/2002
Human Resources Mindy Martin 01/26/2002
Information Services Ashvini Sharma 01/05/2002
Information Services Jean Trenary 01/12/2002
Information Services Janaina Bueno 01/24/2002
Marketing Kevin Brown 02/26/2000
Marketing David Bradley 01/20/2001
Marketing Sariya Harnpadoungsataya 01/13/2002
Production Guy Gilbert 07/31/1999
Production Lynne Dobney 01/26/2001
Production Ruth Ellerbrock 02/06/2001
Production Control Peter Krebs 01/02/2002
Production Control A. Scott Wright 01/13/2002
Production Control William Vong 02/08/2002
Purchasing Mikael Sandberg 03/14/2002
Purchasing Arvind Rao 04/01/2002
Purchasing Linda Meisner 01/18/2003
Quality Assurance Peng Wu 01/10/2002
Quality Assurance Sean Alexander 01/29/2002
Quality Assurance Mark Harrington 02/16/2002
Research and Development Diane Margheim 01/30/2002
Research and Development Gigi Matthew 02/17/2002
Research and Development Dylan Miller 03/12/2002
Sales Stephen Jiang 02/04/2004
Sales Brian Welcker 03/18/2004
Sales Michael Blythe 07/01/2004
Sales Linda Mitchell 07/01/2004
Sales Jillian Carson 07/01/2004
Sales Garrett Vargas 07/01/2004
Sales Tsvi Reiter 07/01/2004
Sales Pamela Ansman-Wolfe 07/01/2004
Sales Shu Ito 07/01/2004
Sales José Saraiva 07/01/2004
Sales David Campbell 07/01/2004
Shipping and Receiving Susan Eaton 01/08/2002
Shipping and Receiving Vamsi Kuppa 01/08/2002
Shipping and Receiving Kim Ralls 01/27/2002
Tool Design Rob Walters 01/05/2001
Tool Design Thierry D'Hers 01/11/2001
Tool Design Ovidiu Cracium 01/05/2004