Posted by Skip on 08/23/06 17:19

Thanks! Works great

Bobbo wrote:
> Skip wrote:
>
> > Sample Data:
> > id hole_name Mtime
> > 156140 2a000020x1 2006-08-02 19:18:34.000
> > 156141 2a000021x1 2006-08-02 19:19:45.000
> > 156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this
> > record
> > 156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only
> > 156144 2a000023x1 2006-08-02 19:29:53.000
> >
> > This works but I cannot include any additional fields:
>
> Ah, I get you. Try doing the max() and GROUP thing as a subquery and
> link it back out to your larger result set, something like this:
>
> select r1.[id], r1.hole_name, r1.mtime, etc...
> from rcompl r1
> where mtime = (select max(mtime) from rcompl r2 where r1.hole_name =
> r2.hole_name)
>
> Or alternatively this (someone else might be able to advise which of
> these is better):
>
> select r1.[id], r1.hole_name, r1.mtime, etc...
> from rcompl r1
> join (select hole_name, max(mtime) from rcompl group by hole_name) r2
> on (r1.hole_name = r2.hole_name and r1.mtime = r2.mtime)

• Next in forum: Efficient Store Proc for Paging Very large DataSet using Cursor Approach
• Prev in forum: Re: Max Row Date
• Thread view: Re: Max Row Date

[Reply to this message]