a small SQL Puzzle

[Fiori]

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

[Roy Harvey (SQL Server MVP)]

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 <da*@xs4all.nlwrote:

>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

[Fiori]

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