SQLite delete the "worst" duplicates-CodePudding

I have this table:

CREATE TABLE `laptimes` (
`driver`    varchar NOT NULL,
`car`   varchar NOT NULL,
`laptimeMs` int NOT NULL,
);

filled with data like in this example:

| driver| car   | laptimeMs|
| ----- | ----- | ---------|
| John  | Toyota| 99999    |
| john  | Toyota| 100000   |
| Bob   | Ford  | 123456   |

As you can see I messed up big time by not taking care of the letter cases in the driver column so now I have driver car combination duplicates like in the example. I want to delete those duplicates which have worse (bigger) laptimeMs values. The closest I could get to what I want is using this query:

delete from laptimes
where rowid not in
         (
         select min(rowid)
         from laptimes
         group by driver COLLATE NOCASE, car
         )

But of course this will simply delete the first duplicate entries it finds, it doesn't care about the laptimeMs value. I thought replacing the select min(rowid) part with select max(laptimeMs) would do the trick but that doesn't work, it just wipes all data. Then I tried to add order by laptimeMs desc like this

delete from laptimes
where rowid not in
         (
         select min(rowid)
         from laptimes
         group by driver COLLATE NOCASE, car
         order by laptimeMs desc
         )

That didn't seem to make any difference.

Can someone help me with this please? Just to be clear - the middle row with value 100000 is the one I want to get rid of in the example.

Edit: The answer by @Tim Biegeleisen was close to what I needed. Exact query that worked for me:

DELETE
FROM laptimes
WHERE EXISTS (
    SELECT 1
    FROM laptimes t
    WHERE t.driver = laptimes.driver COLLATE NOCASE AND
          t.car = laptimes.car AND
          t.laptimeMs < laptimes.laptimeMs
);

CodePudding user response：

I would use exists logic here:

DELETE
FROM laptimes
WHERE EXISTS (
    SELECT 1
    FROM laptimes t
    WHERE t.driver = laptimes.driver AND
          t.laptimeMs < laptimes.laptimeMs
);