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

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote:

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

"LVP" wrote: