I have a table that tracks machine maintenance which happen at arbitrary points in time. Here's a simplified table structure:

```
Maintenance Table
----------------------------------------
ID            - integer
DateCompleted - date
MachineName   - varchar

```

and here's some sample table data:

```
ID   DateCompleted MachineName
----------------------------------------
1     1/6/2011     'Machine 1'
2     1/13/2011    'Machine 2'
3     1/14/2011    'Machine 1'
4     2/2/2011     'Machine 3'
5     2/26/2011    'Machine 1'
6     3/9/2011     'Machine 2'
7     4/20/2011    'Machine 3'

```

What I'm trying to do is create a query that will return the date from the previous maintenance task for each task. So the result set would be like this:

```
ID   MachineName  CurDate     PrevDate
----------------------------------------
1    'Machine 1'    1/6/2011    NULL
2    'Machine 2'    1/13/2011   NULL
3    'Machine 1'    1/14/2011   1/6/2011
4    'Machine 3'    2/2/2011    NULL
5    'Machine 1'    2/26/2011   1/14/2011
6    'Machine 2'    3/9/2011    1/13/2011
7    'Machine 3'    4/20/2011   2/2/2011

```

What would be the best way to write such a query? My only idea so far would be something like this:

```
SELECT ID, MachineName, DateCompleted AS CurDate,
  (
    SELECT TOP 1 DateCompleted FROM Maintenance m2
    WHERE m1.MachineName = m2.MachineName
      AND m1.DateCompleted > m2.DateCompleted
    ORDER BY DateCompleted DESC
  ) AS PrevDate

FROM Maintenance m1

ORDER BY ID

```

Any thoughts, suggestions, or corrections would be very welcome.