Re: sql query - easy problem

From: Guido Konsolke <Guido.Konsolke_at_triaton.com>
Date: Wed, 17 Sep 2003 14:15:12 +0200
Message-ID: <1063800571.559922@news.thyssen.com>

"Christoph Purrucker" wrote...
>
> Given the following table:
>
> Product, Type, Date, <... many more cols ...>

Bad idea: ^^^^ ^^^^

> aaa x 2000, ...
> aaa x 2001, ...
> aaa y 2001, ...
> * aaa z 2003, ...
> * bbb z 2002, ...
> bbb x 2001, ...
> ccc x 2000, ...
> ccc x 2002, ...
> ** ccc x 2003, ...
> *** ccc z 2003, ...
>
> I want to have only the row of each product which
> is the most recent (here marked by *).
> When there are Products with the same date I still
> want to have exactly one row (here either ** or ***
> but not both).
>

Hi Christoph,

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

13:30:32 to85e>select * from gktest;

PRO P DATU
--- - ----
aaa x 2000
aaa x 2001
aaa y 2001
aaa z 2003
bbb z 2002
bbb x 2001
ccc x 2000
ccc x 2002
ccc x 2003
ccc z 2003

10 rows selected.

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

 real: 70

If you're on Oracle > 7, Read The Fine Manuals for tuning this s**t.

hth,
Guido Received on Wed Sep 17 2003 - 07:15:12 CDT