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.
Duncan

Re: a small SQL Puzzle

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

On Wed, 26 Sep 2007 20:37:12 +0200, Fiori wrote:

>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

Re: a small SQL Puzzle

Thank you.

Roy Harvey (SQL Server MVP) schreef:
> 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
>