[Info-Ingres] Impossible in one sql statement??? - an easy one for somebody

[Mark Luijendijk]

Allan,

I think Karl's solution is fine, but this is the simpler way he referred to:

SELECT a.rule,
       a.off_date
FROM   typrul_t_eng a
WHERE  a.off_date =
      (SELECT max(b.off_date)
       FROM   typrul_t_eng b
       WHERE  b.rule = a.rule);

You should check which one performs best with large volumes of data.

Mark

On 8/20/07, Karl & Betty Schendel <schendel at kbcomputer.com> wrote:
>
> At 4:12 PM +0100 8/20/07, Allan Biggs wrote:
> >I think that this is impossible in one SQL statement - perhaps somebody
> can tell me otherwise
> >
> >In the example below what I really want are the rows with the maximum
> off_date where there are more than one row for the same rule
> >i.e. rule ABDELNBP would only have the row for 20-aug-2007
>
> I'm glad to see you say ROWS instead of ROW ...
>
> select t1.rule, t1.off_date
> from typrul_t_eng t1, typrul_t_eng t2
> where t1.rule = t2.rule
> group by t1.rule, t1.off_date
> having t1.off_date = max(t2.off_date)
>
>
> (untested! use at your own risk!  there might be
> a simpler way!  etc...)
>
> Karl
>
>
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres at kettleriverconsulting.com
> http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.kettleriverconsulting.com/mailman/private/info-ingres/attachments/20070820/a587e772/attachment.html