Delete the row with the highest date difference-CodePudding

I have a table

student_id INTEGER PRIMARY KEY AUTOINCREMENT,
name varchar(255)  NOT NULL,
surname varchar(255)  NOT NULL,
dateOfBirth DATE NOT NULL,
yearEnrolled YEAR NOT NULL);

with values

INSERT INTO student (name, surname, dateOfbirth, yearEnrolled)
VALUES
("Walter", "Ananas","1998-05-25",2020),
("Wander", "Ananas","1998-07-24",2019),
("Andrew", "Maxwell","1999-09-25",2020),
("Philip", "Plein","1997-05-15",2020),
("Wayne", "Johnson","1998-02-15",2020),
("Chalton", "Cole","1998-07-29",2018),
("Moise", "Kevin","2000-11-25",2021),
("Wiktor", "Watermelon","1998-12-15",2021),
("Jean-Pierre", "Care","1998-01-22",2020),
("Anthony", "Martial","1996-05-13",2018),
("Wilt", "Chamberlain","1997-05-25",2017),
("Donnavan", "Wiston","1995-05-15",2017),
("Lothar", "Mathews","1999-03-05",2020),
("Ant", "Fibonacci","2001-11-13",2021),
("Kelton", "Mansour","1997-12-15",2021),
("Jean-Pierre", "Cordone","1996-11-22",2020),
("Anthony", "Morinson","1997-05-18",2018),
("Balwain", "Chamberlain","1999-11-25",2018),
("Donnavan", "Konwar","1997-05-22",2017),
("Lothar", "Ans","1995-03-16",2016),
("Walter", "Doile","1994-05-17",2017);

I would like to delete the row with the oldest student.

I am able to get the ages with the following command but somehow I cant combine it with the DELETE stament

DELETE FROM student
WHERE SELECT dateOfBirth, DATE('now')- dateOfBirth as age
FROM student;

I am using SQL lite

CodePudding user response：

This would remove the first of the MAX result if there are some with the same age:

DELETE FROM student
WHERE 
student_id  =
SELECT student_id , MAX(age) FROM (
SELECT student_id, cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dateOfBirth) as int) as age FROM student
)

This would remove all with the MAX age:

DELETE FROM student
WHERE 
cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dateOfBirth) as int) =
SELECT MAX(age) FROM (
SELECT cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', dateOfBirth) as int) as age FROM student
)