SQL - How to I pick a record with a field that has the most current date in a particular column

I have a table that have duplicate lastname,  I need to pick up a record that have their dateOfBirth that is closes to the current date.

I know I need to partition the table base on lastname and then do a sort or ranking based on date of Birth.

How do I do that? I am not sure how to do a sort and rank to get the result?
tommym121Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
If all you need are lastname and DOB:

SELECT lastname, MAX(DOB) AS DOB
FROM SomeTable
GROUP BY lastname

Open in new window


If you need the entire record:

SELECT t1.*
FROM SomeTable t1 INNER JOIN
    (SELECT t2.lastname, MAX(t2.DOB) AS DOB
    FROM SomeTable t2
    GROU BY t2.lastname) z ON t1.lastname = z.lastname AND t1.DOB = z.DOB

Open in new window

0
 
bevhostConnect With a Mentor Commented:
SELECT * FROM bla WHERE lastname='foo' ORDER BY dateOfBirth DESC
0
 
Evan CutlerConnect With a Mentor Volunteer Chief Information OfficerCommented:
Select lastname, mydate, min(datediff(date(), mydate) from bla WHERE lastname='foo';
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
keyuConnect With a Mentor Commented:
select lastname,min(datediff(date(), mydate)) from table_name group by lastname
0
 
aplusexpertConnect With a Mentor Commented:
I think there is no need of ranking and partition

this query should work

SELECT DISTINCT LASTNAME FROM TABLEA
WHERE DOB > GETDATE()
Order by DOB

Thanks.
0
 
tommym121Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.