Difference between revisions of "Select the oldest person"
From SQLZOO
| Line 1: | Line 1: | ||
How can I select the oldest person in the table PERSON by birthday? | How can I select the oldest person in the table PERSON by birthday? | ||
<div class='ht'> | <div class='ht'> | ||
| − | <div class=params>schema: | + | <div class=params>schema:gisq</div> |
<div> | <div> | ||
I have a table | I have a table | ||
Latest revision as of 15:23, 17 July 2012
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)
