Get last child of parents

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LVP - 28 May 2008 02:11 GMT

I have the need to get the last child of each parent.

Note: eDate if what points me to the Last child not the c1_id

Parent ( pk1_id , etc..)

Child ( c1_id, pk1.id, eDate, type_id)

Need the pattern to do this

tried max(), top 1 etc.. just can not figure it out. been driving me nuts
for half an hour.

Thanks

LVP

Reply to this Message

Linchi Shea - 28 May 2008 03:58 GMT

select c.c1_id, c.pk1_id
from Child c join (
select p.pk1_id, max(c1.eDate) as eDate
from Parent p join Child c1 on p.pk1_id = c1.pk1_id
group by p.pk1_id ) as t
on c.pk1_id = t.pk1_id and c.eDate = t.eDate

Linchi

> I have the need to get the last child of each parent.
>
[quoted text clipped - 12 lines]
>
> LVP

Reply to this Message

LVP - 28 May 2008 18:02 GMT

Linchi,

Thanks for your help, I got the same answer that works from another NG.

Thanks again,

LVP

> select c.c1_id, c.pk1_id
> from Child c join (
[quoted text clipped - 22 lines]
>>
>> LVP

Reply to this Message