Re: SQL for Youngest Sibling or Only Sibling
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 06 Apr 2006 23:49:15 +0200
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_REGISTRATIONS 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
.
- Prev by Date: Re: How to insert auto increment?
- Next by Date: Server: Msg 170, Level 15, State 1
- Previous by thread: Re: How to insert auto increment?
- Next by thread: Server: Msg 170, Level 15, State 1
- Index(es):
Relevant Pages
|
|
