Query to Retrieve Latest Row from each group !!

Hi SQL Query Expert,
My table looks like this:
CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
1 <NULL> ABC Company 4/7/2005
11:10:10 a.m.
2 1 XYZ Company
4/8/2005 10:10:12 a.m.
3 1 AAA Company
4/8/2005 12:10:00 p.m.
4 <NULL> BBB Company 4/8/2005
1:00:00 p.m.
5 4 CCC Company
4/8/2005 2:00:00 p.m.
6 <NULL> DDD Company 4/8/2005
3:00:00 p.m.
Basically record 2 and 3 are childs of record 1. Record 5 is child of record
4. Record 6 is a parent.
Could you please give me an example on how to retrieve rows with CONTRACT_PK
equals to 3, 5 and 6 from the above table?
My goal is to retrieve the latest child row if the parent has children. If
the parent doesn't have child(s), then it retrieves parent row. Here 3 and
5
are all latest child of parent 1 and 4. Parent 6 doesn't have child, so, i
t
should get retrieved too.
The table could have 1000 rows and they all fall in the same pattern for the
records retrieval.
Thank you so much!!!
-adamTry This
Select IsNull(C.CONTRACT_PK, P.CONTRACT_PK) ContractPK,
IsNull(C.PARENT_PK, P.PARENT_PK) ParentPK,
IsNull(C.CONTRACTOR_NAME, P.CONTRACTOR_NAME) Contractor,
IsNull(C.CREATED_DATE, P.CREATED_DATE) CreatedDate
From Table P
Left Join Table C
On C.Parent_PK = P.Contract_PK
And C.Created_Date = (Select Max(Created_Date)
From Table
Where Parent_PK =
C.Parent_PK)
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>|||adam,
Do not post the same problem twice, it does not help. Check your first threa
d.
AMB
"adam" wrote:

> Hi SQL Query Expert,
> My table looks like this:
> CONTRACT_PK PARENT_PK CONTRACTOR_NAME CREATED_DATE
> 1 <NULL> ABC Company 4/7/200
5
> 11:10:10 a.m.
> 2 1 XYZ Company
> 4/8/2005 10:10:12 a.m.
> 3 1 AAA Company
> 4/8/2005 12:10:00 p.m.
> 4 <NULL> BBB Company 4/8/20
05
> 1:00:00 p.m.
> 5 4 CCC Company
> 4/8/2005 2:00:00 p.m.
> 6 <NULL> DDD Company 4/8/200
5
> 3:00:00 p.m.
> Basically record 2 and 3 are childs of record 1. Record 5 is child of reco
rd
> 4. Record 6 is a parent.
> Could you please give me an example on how to retrieve rows with CONTRACT_
PK
> equals to 3, 5 and 6 from the above table?
> My goal is to retrieve the latest child row if the parent has children. I
f
> the parent doesn't have child(s), then it retrieves parent row. Here 3 an
d 5
> are all latest child of parent 1 and 4. Parent 6 doesn't have child, so,
it
> should get retrieved too.
> The table could have 1000 rows and they all fall in the same pattern for t
he
> records retrieval.
> Thank you so much!!!
> -adam
>