|
Home > Archive > MS SQL Server MSEQ > April 2006 > SQL for Youngest Sibling or Only Sibling
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL for Youngest Sibling or Only Sibling
|
|
| Bonnie 2006-04-06, 9:32 am |
| Please Help...I've tried everything I can think of with no luck.
Given the following tables:
PERSONS table with a (unique PERSON_ID), SIBLING_ID, BIRTH_DATE, SURNAME,
FIRST_NAME
STUDENT_REGISTRATION
S table with a (concatenated key PERSON_ID,
SCHOOL_YEAR, SCHOOL_CODE )
I need to select students with a specific school_code = 'NORT' and school
year = '20052006' who have either no siblings (sibling_id will be null) and
youngest student of a sibling group (note surname may not be same as another
family member and there maybe twins having the same birthday.) All siblings
in the same family unit have a unique sibling_id.
For example:
person_id sibling_id birth_date Surname First_Name
1 20 1995-05-05 Doe John
2 null 1992-01-01 Smith Betty
3 20 2000-06-06 Doe Jane
4 20 1999-01-07 Doe Bill
5 30 1998-07-05 Twins Sally
6 30 1998-07-05 Twins Bob
7 30 1996-12-12 Twins-Not Sally
The result set should return (Note: min person_id of twins):
person_id sibling_id birth_date Surname First_Name
3 20 2000-06-06 Doe Jane
2 null 1992-01-01 Smith Betty
5 30 1998-07-05 Twins Sally
| |
| Bonnie 2006-04-06, 8:27 pm |
| I got a working answer from a different news group.
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 BIRTH_DATE DESC,PERSON_ID)
"Bonnie" wrote:
> Please Help...I've tried everything I can think of with no luck.
>
> Given the following tables:
>
> PERSONS table with a (unique PERSON_ID), SIBLING_ID, BIRTH_DATE, SURNAME,
> FIRST_NAME
>
> STUDENT_REGISTRATION
S table with a (concatenated key PERSON_ID,
> SCHOOL_YEAR, SCHOOL_CODE )
>
> I need to select students with a specific school_code = 'NORT' and school
> year = '20052006' who have either no siblings (sibling_id will be null) and
> youngest student of a sibling group (note surname may not be same as another
> family member and there maybe twins having the same birthday.) All siblings
> in the same family unit have a unique sibling_id.
> For example:
>
> person_id sibling_id birth_date Surname First_Name
> 1 20 1995-05-05 Doe John
> 2 null 1992-01-01 Smith Betty
> 3 20 2000-06-06 Doe Jane
> 4 20 1999-01-07 Doe Bill
> 5 30 1998-07-05 Twins Sally
> 6 30 1998-07-05 Twins Bob
> 7 30 1996-12-12 Twins-Not Sally
>
> The result set should return (Note: min person_id of twins):
> person_id sibling_id birth_date Surname First_Name
> 3 20 2000-06-06 Doe Jane
> 2 null 1992-01-01 Smith Betty
> 5 30 1998-07-05 Twins Sally
| |
| Hugo Kornelis 2006-04-06, 8:27 pm |
| On Thu, 6 Apr 2006 06:26:02 -0700, Bonnie wrote:
>Given the following tables:
>
>PERSONS table with a (unique PERSON_ID), SIBLING_ID, BIRTH_DATE, SURNAME,
>FIRST_NAME
>
> STUDENT_REGISTRATION
S table with a (concatenated key PERSON_ID,
>SCHOOL_YEAR, SCHOOL_CODE )
>
>I need to select students with a specific school_code = 'NORT' and school
>year = '20052006' who have either no siblings (sibling_id will be null) and
>youngest student of a sibling group (note surname may not be same as another
>family member and there maybe twins having the same birthday.) All siblings
>in the same family unit have a unique sibling_id.
>For example:
>
>person_id sibling_id birth_date Surname First_Name
>1 20 1995-05-05 Doe John
>2 null 1992-01-01 Smith Betty
>3 20 2000-06-06 Doe Jane
>4 20 1999-01-07 Doe Bill
>5 30 1998-07-05 Twins Sally
>6 30 1998-07-05 Twins Bob
>7 30 1996-12-12 Twins-Not Sally
>
>The result set should return (Note: min person_id of twins):
>person_id sibling_id birth_date Surname First_Name
>3 20 2000-06-06 Doe Jane
>2 null 1992-01-01 Smith Betty
>5 30 1998-07-05 Twins Sally
Hi Bonnie,
Do I smell a school assignment?
Anyway, the specifications you've given are incomplete. You write that
twins might have the same birthday, but you fail to tell how to select
the twin to put in the result set. In other words, why was Sally Twins
included in the result above, and not Bob Twins?
For better help, please post CREATE TABLE and INSERT statements, as
explained at www.aspfaq.com/5006.
--
Hugo Kornelis, SQL Server MVP
|
|
|
|
|