Assuming your table looks like this --
cID cStart cEnd cEmpl 122 2002-01-01 2002-03-31 john 123 2002-01-01 2002-02-28 fred 124 2002-01-01 2002-09-30 bill 125 2002-04-01 2002-04-30 fred 126 2002-05-01 2002-05-31 fred 127 2002-04-01 2002-06-30 john 128 2002-11-01 2002-12-31 bill
The query must find the latest contract for each employee, and since one contract doesn't start until the previous one ends, we can use MAX(cStart). Using the start date rather than end date is preferable, since this allows ongoing contracts to have a null end date, although this might not apply in your situation.
However, that's not enough to give the entire contract period. You need both the start date and the end date. But how can you get the end date when selecting MAX(cStart)? The answer is to use a correlated
select cID, cStart, cEnd, cEmpl from yourtable XX where cStart = ( select max(cStart) from yourtable where cEmpl = XX.cEmpl )
Here, the correlation is on the employee column. The outer query's WHERE clause accepts only those rows where cStart is the highest for that employee.
There's another way to do this, using a derived table in a join; see Rows having maximum group value in MySQL. Note that in MySQl, you have to do it in two steps using a temp table.
This was first published in November 2002