a small SQL Puzzle

Hi,

Probable there is a simple solution for this, hopefully someone can
direct me in the right direction.

I have a table with a persons firstname, lastname, birthdate and
address. However, I want to select only one person per address, namely
the eldest of all persons living on the same address.

Can anyone provide me a solution?

Thanks in advance.
DuncanUnless there are twins who are both the oldest this will do what you
ask.

SELECT *
FROM SomeTable as A
WHERE birthdate =
(SELECT MIN(birthdate)
FROM SomeTable as B
WHERE A.address = B.address)

Roy Harvey
Beacon Falls, CT

On Wed, 26 Sep 2007 20:37:12 +0200, Fiori <dab@.xs4all.nlwrote:

Quote:

Originally Posted by

>Hi,
>
>Probable there is a simple solution for this, hopefully someone can
>direct me in the right direction.
>
>I have a table with a persons firstname, lastname, birthdate and
>address. However, I want to select only one person per address, namely
>the eldest of all persons living on the same address.
>
>Can anyone provide me a solution?
>
>Thanks in advance.
>Duncan

|||Thank you.

Roy Harvey (SQL Server MVP) schreef:

Quote:

Originally Posted by

Unless there are twins who are both the oldest this will do what you
ask.
>
SELECT *
FROM SomeTable as A
WHERE birthdate =
(SELECT MIN(birthdate)
FROM SomeTable as B
WHERE A.address = B.address)
>
Roy Harvey
Beacon Falls, CT
>