Pass-through Query to MS SQL dB

[Bonnie]

Please Help: There must be a SQL solution to retrieve the youngest and only
siblings. I thought this query would be pretty straight forward but
apparently NOT. Everything I tried fails to extract the correct result set.

Environment: MS ACCESS 2003 pass-through query to a MS SQL Server 2000 DB.

Criteria: Select students without any siblings (SIBLING_CODE IS NULL OR
='') and only the youngest sibling of a family MAX(BIRTHDATE). Note:
Siblings will have a unique

family number. Siblings could be a twins with same birthdate SELECT
MIN(PERSON_ID).

Members of a family may have different surnames. GROUP BY SIBLING_CODE but
also select the PERSON_ID for other multiple table links.


PERSONS table with select fields defined:

PERSON_ID (varchar (13) Unique not null)
SIBLING_CODE (number (9)(13,0) NULL)
SURNAME (varchar (40) not null)
FIRST (varchar (20) not null)
BIRTHDATE (DATETIME (8) null)
..
..
..

Sample data:

PERSONS table

PERSON_ID | SIBLING_CODE | SURNAME | FIRST | BIRTHDATE
-------------------------------------------------------
100 |1 | Doe | John | 1996-01-01
200 |1 | Doe | Sally | 2000-02-01
300 |<NULL> | Smith | Jane | 1999-02-03
400 |2 | Twins | Joe | 2000-12-25
500 |2 | Twins | Moe | 2000-12-25
600 |2 | G-Twins | Bill | 1997-09-05

 

[giorgio rancati]

Hi Bonnie,
try this
----
SELECT *
FROM PERSON
WHERE SIBLING_CODE IS NULL
OR PERSON_ID = (SELECT TOP 1 PERSON_ID
FROM PERSON P
WHERE P.SIBLING_CODE=PERSON.SIBLING_CODE
ORDER BY BIRTHDATE DESC,PERSON_ID)

 

[Tom Ellison]

Dear Bonnie:

Except for the choice of one twin over the other, this is fairly straight
forward. But the solution will show both the twins. We can work to refine
that later.

SELECT PERSON_ID, SIBLING_CODE,
SURNAME, FIRST, BIRTHDATE
FROM PERSONS T
WHERE BIRTHDATE = (
SELECT MAX(BIRTHDATE)
FROM PERSONS T1
WHERE T1.SIBLING_CODE = T.SIBLING_CODE)

Now, on what basis do you want to choose one of the twins over the other?

Tom Ellison

 

[Bonnie]

Thank you Tom, I tried your script and it worked other than the twins as you
said.

The prior posted SQL reply by Giorgio Rancati dealt with the twins by
ordering the person_id so the first twin entered into db was selected in the
TOP statement.

SELECT *
FROM PERSON
WHERE SIBLING_CODE IS NULL
OR PERSON_ID = (SELECT TOP 1 PERSON_ID
FROM PERSON P
WHERE P.SIBLING_CODE=PERSON.SIBLING_CODE
ORDER BY BIRTHDATE DESC,PERSON_ID)

Dear Bonnie:

Except for the choice of one twin over the other, this is fairly straight
forward. But the solution will show both the twins. We can work to refine
that later.

SELECT PERSON_ID, SIBLING_CODE,
SURNAME, FIRST, BIRTHDATE
FROM PERSONS T
WHERE BIRTHDATE = (
SELECT MAX(BIRTHDATE)
FROM PERSONS T1
WHERE T1.SIBLING_CODE = T.SIBLING_CODE)

Now, on what basis do you want to choose one of the twins over the other?

Tom Ellison

 

[Tom Ellison]

Dear Bonnie:

I saw his post and admire that solution to the problem, while not knowing if
that's what you want. In many ways, it's just an arbitrary way of selecting
one of the two twins and not the other. If that's what you want, great. I
just didn't make that same assumption, but you saw where I expected the
results you saw, and suggested we could solve that if it's a problem and you
know how you want it solved.

Enjoy!

Tom Ellison

Thank you Tom, I tried your script and it worked other than the twins as
you
said.

The prior posted SQL reply by Giorgio Rancati dealt with the twins by
ordering the person_id so the first twin entered into db was selected in
the
TOP statement.

SELECT *
FROM PERSON
WHERE SIBLING_CODE IS NULL
OR PERSON_ID = (SELECT TOP 1 PERSON_ID
FROM PERSON P
WHERE P.SIBLING_CODE=PERSON.SIBLING_CODE
ORDER BY BIRTHDATE DESC,PERSON_ID)