SQL Server How can I calculate the oldest age for a male person given their date of birth in SQL?

xxls0lw8  于 2023-06-04  发布在  其他
关注(0)|答案(2)|浏览(167)

How to calculate maximum age of a male person from a list containing date of birth in sql?

I tried

Select name, datediff(yy, dateofbirth, getdate()) as age
From table 
Where gender = male

But I don’t know how to use the max function to get the max age

bvjveswy

bvjveswy1#

Do something like this to get the least recent date of birth.

SELECT name, datediff(yy, dateofbirth, getdate()) as age, dateofbirth
  FROM tbl
 WHERE gender = 'm'
 ORDER BY dateofbirth
 LIMIT 1

If you use Microsoft SQL Server, you do this.

SELECT TOP (1)
       name, datediff(yy, dateofbirth, getdate()) as age, dateofbirth
  FROM tbl
 WHERE gender = 'm'
 ORDER BY dateofbirth
7ajki6be

7ajki6be2#

If we assume everyone in the table is still alive, then the oldest male has the MIN(DOB) . You did not specify any date of death so there you go.

Then you only need to calculate their age.

Something along the lines of:

WITH CTE AS (
SELECT MIN(DOB) AS [minDOB]
FROM table
WHERE gender = 'male'
) --CTE
SELECT *
      ,[Age] = floor((datediff(day,0,@today) - datediff(day,0,@birthdate)) / 365.2425) 
  FROM table
 WHERE DOB = (SELECT [minDOB] FROM CTE) AND gender = 'male'

For the age algorithm I used brianary's answer on a different stackoverflow thread at How to calculate age (in years) based on Date of Birth and getDate()

Feel free to upvote his answer.

相关问题