(SQL) find oldest person in table (avoid hard code)

Question

I'm trying to find the oldest person at my table. And I have all person's birthdates.

I write code like this:

SELECT TIMESTAMPDIFF(day, birthdate, '2021-09-12') AS 'Age'
FROM table
ORDER BY age
limit 1

but I think this is hard-coding. How can I improve my code?

jurez · Answer 1 · 2021-09-12T16:37:48.703

2

The oldest person would be the one with minimum birth date. You don't need TIMESTAMPDIFF at all, you just order by birthdate asc and take limit 1.

edited Sep 12 '21 at 16:37

answered Sep 12 '21 at 16:35

jurez

4,436
2
12
20

1

You want ascending not descending, otherwise you'll end up with the youngest. – Monofuse Sep 12 '21 at 16:37

score 1 · Answer 2 · answered Sep 12 '21 at 16:36

1

You don't need the timestampdiff - just order the query by birthdate and take the smallest (earliest) one:

SELECT   *
FROM     mytable
ORDER BY birthdate ASC
LIMIT    1

answered Sep 12 '21 at 16:36

Mureinik

297,002
52
306
350

1

Thank you!! Also may I use to code 'MIN(DATE)'? – island1996 Sep 12 '21 at 16:36
@island1996 you don't need a `min` call there - just query the `birthdate` - since you're ordering and taking the first row, it will be the smallest. – Mureinik Sep 12 '21 at 16:37

score 0 · Answer 3 · answered Sep 12 '21 at 16:40

0

No need to calculate the age in days for this purpose, as stated by others. However, if you wanted to continue with your solution, which calculates the age of a person in days, you could deploy the following solution:

SELECT TIMESTAMPDIFF(day, birthdate, CURRENT_TIMESTAMP) AS 'Age' 
FROM table ORDER BY age DESC limit 1

answered Sep 12 '21 at 16:40

Rui Reis

43
6

Thank you!!! And may I ask if I calculate the age, is my code a hard code? – island1996 Sep 12 '21 at 16:41
No. When we refer to some values as "hard coded" we mean to say that providing specific values, such as today's date in your example, reduces the code's flexibity. This new solution is not hard coded since the `CURRENT_TIMESTAMP` adapts to the current timestamp. – Rui Reis Sep 12 '21 at 16:47

score 0 · Answer 4 · answered Sep 12 '21 at 16:42

0

you should also change the fixed date to curdate() which would automatically select the actual date

SELECT TIMESTAMPDIFF(day, birthdate, CURDATE()) AS 'Age'
FROM table1
ORDER BY birthdate DESC
limit 1

answered Sep 12 '21 at 16:42

nbk

45,398
8
30
47

(SQL) find oldest person in table (avoid hard code)

4 Answers4