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          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.