Re: sql query - easy problem

"Guido Konsolke" wrote...
> "Christoph Purrucker" wrote...
> > Given the following table:
> >
> > Product, Type, Date, <... many more cols ...>
>
> Bad idea: ^^^^ ^^^^

"Is net auf meinem Mist gewachsen!"

> sad but true: you didn't come up with any infos
> about your environment (dbms version etc.).
> So this is some <8i stuff:

oh, sorry, I'm on "Oracle8i Enterprise Edition Release 8.1.7.0.0"

> select min(PRODUCT) product,
> min(PRODTYPE) prodtype,
> min(DATUM) datum
> from gktest g1
> where datum=(select max(datum) from gktest g2
> where g1.product = g2.product)
> group by product;
> PRO P DATU
> --- - ----
> aaa z 2003
> bbb z 2002
> ccc x 2003

This is the make-it-straight-forward. Can You imagine any other solution, so I do not have to repeat the min(x) for every column? Remember the "<... many more cols ...>".

Currently I only have to find out the current type of a product so I use:

select distinct last_value( type ) over (

    partition by product order by date asc nulls first     rows between unbounded preceding and unbounded following   ) as latest_type
  from myTable
  where product = 'x';

But my version is also inflexible.

Thanks so far! More hints always welcome.

Regards,

cp Received on Wed Sep 17 2003 - 08:12:36 CDT