SQL: How to get youngest and oldest from a person table

less than 1 minute read

Problem: To fetch the youngest and oldest of the family based on the last name from the “Person” table whose design is as given below in a single query

Data

Solution :

select ActualTable.FirstName,ActualTable.LastName, 
    DATEDIFF(YY,ActualTable.DateOfBirth,GETDATE()) as Age        
from 
    Person as ActualTable,
    (    select MAX(DateOfBirth) young,MIN(DateOfBirth) old,lastname 
        from Person 
        group by LastName
    ) as AggragatedTable
where 
    ActualTable.DateOfBirth = AggragatedTable.young Or 
    ActualTable.DateOfBirth = AggragatedTable.old and 
    ActualTable.LastName = AggragatedTable.LastName        
order by ActualTable.LastName, Age desc

Here i have used an aggregated result from the person table.

Tags: ,

Categories:

Updated:

Leave a comment