Querying oldest from a birthdate

[ptrifile]

I need to create a query that will only pull the oldest person from a table. My table holds, NAME, CASE#, and DATE OF BIRTH. My problem is that there may be 10 different people with the same CASE# but I only want to have the query pull the person who is the oldest. Can someone please show me how to do this.....

Thank You!!!

Paul

 

[fredericofonseca]

Depending on your database you may be able to use the "top" option.

Otherwise use a select from ... where date = (select min(date) from ...)

Note that if you have several individuals with the same date the second option will eventualy return more than one record, while the first will return only 1.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DotNetGnat]

Does this work??

SELECT TOP 1 NAME, CASE#, BIRTHDATE FROM YOURTABLE ORDER BY BIRTHDATE ASC

-DNG

 

[PHV]

A starting point:
SELECT A.[CASE#], A.NAME, A.[DATE OF BIRTH]
FROM yourTable AS A INNER JOIN (
SELECT [CASE#], Min([DATE OF BIRTH]) AS Oldest FROM yourTable GROUP BY [CASE#]
) AS B ON A.[CASE#] = B.[CASE#] AND A.[DATE OF BIRTH] = B.Oldest;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ptrifile]

Thank you all for your responses!!!!!

PHV, your suggestion worked GREAT!!! However I do have one question, I seem to have quite a few duplicate records, is there something that I can add to the query to supress the duplicate records?

Thanks again!!!!!


Paul

 

[PHV]

SELECT A.[CASE#], Min(A.NAME) AS [a Name], A.[DATE OF BIRTH]
FROM yourTable AS A INNER JOIN (
SELECT [CASE#], Min([DATE OF BIRTH]) AS Oldest FROM yourTable GROUP BY [CASE#]
) AS B ON A.[CASE#] = B.[CASE#] AND A.[DATE OF BIRTH] = B.Oldest
GROUP BY A.[CASE#], A.[DATE OF BIRTH];

You may replace Min by Max or First or Last or ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ptrifile]

PHV, thank you! this worked fantastic, but now i run into another problem because of this and wondered if you have a suggestion. This query feeds a form, I need to add a yes/no check box on the form to update the table, however when i try this the check box will not populate. Any suggestions?

Thanks!!

Paul

 

[ptrifile]

Figured it out. Thanks to all who posted!

Paul