Consider an exam record table exam_record that logs user attempts over several year. The table has the folowing structure:

The goal is to delete exactly three records from exam_record: those with the earliest start_time among rows where the attempt was either not completed (submit_time is NULL) or where the answering duration was strictly less than five minutes. After the deletion, runing SELECT * FROM exam_record should yield the remaining records.

Initial data after table creation and insertion:

DROP TABLE IF EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Auto-increment ID',
  uid INT NOT NULL COMMENT 'User ID',
  exam_id INT NOT NULL COMMENT 'Exam ID',
  start_time DATETIME NOT NULL COMMENT 'Start time',
  submit_time DATETIME COMMENT 'Submit time',
  score TINYINT COMMENT 'Score'
) CHARACTER SET utf8 COLLATE utf8_general_ci;

TRUNCATE exam_record;
INSERT INTO exam_record(uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-01 22:11:12', '2020-01-01 23:16:12', 50),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:06:00', 58),
(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:05:01', 58),
(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:06:58', 60),
(1002, 9002, '2021-06-02 19:01:01', NULL, NULL),
(1003, 9001, '2021-09-05 19:01:01', NULL, NULL),
(1003, 9001, '2021-09-05 19:01:01', NULL, NULL),
(1003, 9002, '2021-09-09 07:01:02', NULL, NULL);

Expected result after deletion (output of SELECT * FROM exam_record):

1|1001|9001|2020-01-01 22:11:12|2020-01-01 23:16:12|50
4|1002|9001|2021-05-02 10:01:01|2021-05-02 10:06:58|60
6|1003|9001|2021-09-05 19:01:01|None|None
7|1003|9001|2021-09-05 19:01:01|None|None
8|1003|9002|2021-09-09 07:01:02|None|None

This task illustrates a more advanced use of the DELETE statement: you can filter with WHERE, order rows with ORDER BY, and restrict the number of deleted rows with LIMIT, much like in SELECT. The LIMIT 3 clause removes exactly the first three matching rows according to the specified order.

One straightforward solution uses the standard DELETE with ordering and limit:

DELETE FROM exam_record
WHERE submit_time IS NULL
   OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
ORDER BY start_time
LIMIT 3;

An alternative approach leverages a common table expression with a window function to achieve the same effect. This changes the internal logic and naming while producing identical results. The CTE first numbers the qualifying rows in order of start_time, then deletes those with a row number ≤ 3:

WITH candidates AS (
  SELECT id,
         ROW_NUMBER() OVER (ORDER BY start_time) AS seq
  FROM exam_record
  WHERE submit_time IS NULL
     OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
)
DELETE FROM exam_record
WHERE id IN (SELECT id FROM candidates WHERE seq <= 3);

Both queries correctly remove the three earliest incomplete or short‑duration attempts while leaving the remaining records intact.