[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-05
6
Medium Priority
?
585 Views
Last Modified: 2012-09-09
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?
0
Comment
Question by:tommym121
6 Comments
 
LVL 19

Assisted Solution

by:bevhost
bevhost earned 300 total points
ID: 38370011
SELECT * FROM bla WHERE lastname='foo' ORDER BY dateOfBirth DESC
0
 
LVL 9

Assisted Solution

by:Evan Cutler
Evan Cutler earned 300 total points
ID: 38370035
Select lastname, mydate, min(datediff(date(), mydate) from bla WHERE lastname='foo';
0
 
LVL 9

Assisted Solution

by:keyu
keyu earned 300 total points
ID: 38371483
select lastname,min(datediff(date(), mydate)) from table_name group by lastname
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:aplusexpert
aplusexpert earned 300 total points
ID: 38372150
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 800 total points
ID: 38372547
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
 

Author Closing Comment

by:tommym121
ID: 38380332
Thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question