How do I write a standard SQL GROUP BY that includes columns not in the GROUP BY clause

Let's say I have a table called Customer, defined like this:

Id       Name       DepartmentId    Hired
1        X          101             2001/01/01
2        Y          102             2002/01/01
3        Z开发者_如何学Go          102             2003/01/01

And I want to retrieve the date of the last hiring in each department.

Obviously I would do this

SELECT c.DepartmentId, MAX(c.Hired)
  FROM Customer c
 GROUP BY c.DepartmentId

Which returns:

101      2001/01/01
102      2003/01/01

But what do I do if I want to return the name of the guy hired? I.e. I would want this result set:

101      2001/01/01       X
102      2003/01/01       Z

Note that the following does not work, as it would return three rows rather than the two I'm looking for:

SELECT c.DepartmentId, c.Name, MAX(c.Hired)
  FROM Customer c
 GROUP BY c.DepartmentId

I can't remember seeing a query that achieves this.

NOTE: It's not acceptable to join on the Hired field, as that would not be guaranteed to be accurate.

A subselect would do the job and would handle the case where more than one person was hired in the same department on the same day:

SELECT c.DepartmentId, c.Name, c.Hired from Customer c,
(SELECT DepartmentId, MAX(Hired) as MaxHired
  FROM Customer
 GROUP BY DepartmentId) as sub
WHERE c.DepartmentId = sub.DepartmentId AND c.Hired = sub.MaxHired

Standard Sql:

select * 
from Customer C

where exists
(
  -- Linq to Sql put NULL instead ;-) 
  -- In fact, you can even put 1/0 here and would not cause division by zero error
  -- An RDBMS do not parse the select clause of correlated subquery
  SELECT NULL 

  FROM Customer 

  where c.DepartmentId = DepartmentId

  GROUP BY DepartmentId

  having c.Hired = MAX(Hired)
) 

If Sql Server happens to support tuple testing, this is the most succint:

select * 
from Customer 
where  (DepartmentId, Hired) in 

(select DepartmentId, MAX(Hired)
 from Customer 
 group by DepartmentId)

SELECT a.*
FROM Customer AS a
JOIN
(SELECT DepartmentId, MAX(Hired) AS Hired
FROM Customer GROUP BY DepartmentId) AS b
USING (DepartmentId,Hired);