I'm having a bit of an SQL head-scratching moment and was wondering if anyone could help. Using this data (for example):

```
workId     rateRevision
1          1
1          2
1          3
2          1
3          1
3          2
4          1
4          2
4          3

```

... I basically want to delete all duplicates except the latest revision (highest rateRevision value). So I would end up with:

```
workId     rateRevision
1          3
2          1
3          2
4          3

```

Theoretically it sounds really simple but I can't work it out in SQL. Here's my current code:

```
SELECT * FROM rate r
WHERE r.rateRevision = 
(select r.rateRevision from rate r ORDER BY r.rateRevision DESC LIMIT 1);

```

...but that just returns

```
workId     rateRevision
4          3

```

Of course, this is only selecting the values at the moment, but I was planning on essentially just reversing it for the delete statement. Does anyone have any ideas please?

Many thanks!
Matt