a small SQL Puzzle

#1

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

#2

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 <dab (AT) xs4all (DOT) nl> wrote:

Quote:

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

#3

Thank you.

Roy Harvey (SQL Server MVP) schreef:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

a small SQL Puzzle

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

a small SQL Puzzle - 09-26-2007 , 01:37 PM

Re: a small SQL Puzzle - 09-26-2007 , 01:47 PM

Re: a small SQL Puzzle - 09-27-2007 , 01:06 AM