Re: [OT] SQL question

[Doogie Brodie <ukhad@xxxxxxx>]

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
inner join child c on p.parentid=c.parentid 
where c.childid in (
select max(childid) 
>from
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">
> > 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
> ..