RE: [OT] SQL question

["BUTLER, Tony, FM" <tony.butler@xxxxxxx>]

John,

Try this:

SELECT Table1.Parent, Table1.Date, Table1.Comment
>from
INNER JOIN (SELECT Parent, MAX(Date) AS Date from Table1 GROUP BY Parent)
AS
temp
on Table1.Parent=temp.Parent and Table1.Date=temp.Date

Note: I wouldn't call the date field 'date' as this is a reserved work in
some DMBS's
Call it 'CommentDate' or something.

HTH,

Tony


> -----Original Message-----
> From: Dr John Tankard [mailto:john@xxxxxxx]
> Sent: 22 March 2002 18:36
> To: ukha_d@xxxxxxx
> Subject: RE: [ukha_d] [OT] SQL question
>
>
>
> > 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
> > >
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~-->
> Buy Stock for $4.
> No Minimums.
> FREE Money 2002.
> http://us.click.yahoo.com/BgmYkB/VovDAA/ySSFAA/IBOolB/TM
> --------------------------------------------------------------
> -------~->
>
> 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
> http://docs.yahoo.com/info/terms/
>
>


********************************************************************
Visit our Internet site at http://www.rbsmarkets.com

This e-mail is intended only for the addressee named above.
As this e-mail may contain confidential or privileged information,
if you are not the named addressee, you are not authorised to
retain, read, copy or disseminate this message or any part of it.
********************************************************************