RE: [OT] SQL question

["Dr John Tankard" <john@xxxxxxx>]

Title: Message

Thanks, Ray emailed me off list at about the same time with the same sort of solution Two great minds at work ;-)

 

Thanks,  I looked up in my SQL cook book and could not find anything.

 

John (VPN connection to SQL server tonight) Tankard

-----Original Message-----
From: Doogie Brodie [mailto:ukhad@xxxxxxx]
Sent: 22 March 2002 18:53
To: ukha_d@xxxxxxxSubject: Re: [ukha_d] [OT] SQL question

John..... what I'm thinking of is:-

Parent:-

ParentID integer primary key Identity (or autonumber in Access)
Parent Other fields

Child:-

ChildID integer primary key Identity (or autonumber in Access)
ParentID
Comment
Child Other Fields

assuming that the highest childid for each parent record is the most recently inserted (ie looking at the index and not the date, this may cause problems if you take account of backdating.....)

select *
from parent p
inner join child c on p.parentid=c.parentid
where c.childid in (
select max(childid)
from child
group by parentid)

IE - work out what the maximum childid is for each parentid record, then select its data.

HTH

Doogie


Dr John Tankard wrote:

000c01c1d1d0$73b72630$0602a8c0@xxxxxxx type="cite">
> How about using ORDER BY time/date/recordnumber, whatever can
> be used as a distinct incrementing identifier for the child?
>
> Mark.

Thanks Mark

I have not quite got it

Say this is the child data

Parent Date       Comment
1      22/02/02   Some data
1      20/02/02   Some more data with the same parent
2      05/02/02   More data but for a different parent
2      01/02/02   More data for the second parent
3      20/02/02   Data for a third parent

The result set I am after is this
1      22/02/02   Some data
2      05/02/02   More data but for a different parent
3      20/02/02   Data for a third parent

IE the most receint comment only ?


John


>
> > -----Original Message-----
> > From: Dr John Tankard [mailto:john@xxxxxxx]
> > Sent: 22 March 2002 17:39
> > To: ukha_d@xxxxxxx
> > Subject: [ukha_d] [OT] SQL question
> >
> >
> > Sorry for the OT post
> >
> > drastically simplified
> > 2 tables
> > 1 to many relationship
> >
> > I want to select just the most recent child, so for each row in the
> > parent I want to show only one (the most recent) row in the child
> >
> > DISTINCT wont work because the child rows are not identical
> >
> > Any Ideas
> >




For more information: http://www.automatedhome.co.uk
Post message: ukha_d@xxxxxxx
Subscribe:  ukha_d-subscribe@xxxxxxx
Unsubscribe:  ukha_d-unsubscribe@xxxxxxx
List owner:  ukha_d-owner@xxxxxxx

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service .

Yahoo! Groups Sponsor

ADVERTISEMENT

For more information: http://www.automatedhome.co.uk
Post message: ukha_d@xxxxxxx 
Subscribe:  ukha_d-subscribe@xxxxxxx 
Unsubscribe:  ukha_d-unsubscribe@xxxxxxx 
List owner:  ukha_d-owner@xxxxxxx

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.