Select the oldest person
How can I select the oldest person in the table PERSON by birthday?
schema:gisq
I have a table
PERSON(personID, name, sex, birthday, placeOfBirth)
Now I would like to SELECT the oldest person in the table by birthday.
Answer: Use a nested query to find the earliest birthday.
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
name VARCHAR(20),
sex CHAR(1),
birthday DATE,
placOfBirth VARCHAR(20));
INSERT INTO people VALUES
(1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
(2,'Andrew','M','20 May 1962','Hong Kong');
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
name VARCHAR(20),
sex CHAR(1),
birthday DATE,
placOfBirth VARCHAR(20));
INSERT INTO people VALUES
(1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
(2,'Andrew','M','20 May 1962','Hong Kong');
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
name VARCHAR(20),
sex CHAR(1),
birthday DATE,
placOfBirth VARCHAR(20));
INSERT INTO people VALUES
(1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
(2,'Andrew','M','20 May 1962','Hong Kong');
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
name VARCHAR(20),
sex CHAR(1),
birthday DATE,
placOfBirth VARCHAR(20));
INSERT INTO people VALUES
(1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
(2,'Andrew','M','20 May 1962','Hong Kong');
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)
-- Set up the table
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
name VARCHAR(20),
sex CHAR(1),
birthday DATE,
placOfBirth VARCHAR(20));
INSERT INTO people VALUES
(1,'Oliver','M','1985-05-25','Bedford');
INSERT INTO people VALUES
(2,'Andrew','M','1962-05-20','Hong Kong');
--Here is the answer
SELECT * FROM people
ORDER BY birthday ASC
LIMIT 1
-- Set up the problem
DROP TABLE people;
CREATE TABLE people(personId INTEGER PRIMARY KEY,
name VARCHAR(20),
sex CHAR(1),
birthday DATE,
placOfBirth VARCHAR(20));
INSERT INTO people VALUES
(1,'Oliver','M','25 May 1985','Bedford');
INSERT INTO people VALUES
(2,'Andrew','M','20 May 1962','Hong Kong');
-- Here is the answer
SELECT * FROM people
WHERE birthday = (SELECT MIN(birthday) FROM people)