Re: sql query - easy problem

"Christoph Purrucker" <gugi_at_bigfoot.de> wrote in message news:<bk9bfj$mvs$1_at_home.itg.ti.com>...
> Hello,
>
> I am doing SQL since a half year, but now I think I
> have a black out: I dont't find a solution for this
> silly problem:
>
> Given the following table:
>
> Product, Type, Date, <... many more cols ...>
> 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).
>
> I know that this table is not normalized but I get
> it in this form by a flat file... :-(
>
> Thanks.
>
> cp

Hello Christoph,

the following statement should do it:

select t1.product,min(t2.type),t1.date from (select product, max(date) date from table group by product) t1, table t2 where t1.product=t2.product and t1.date=t2.date group by t1.product, t1.date

HTH,
Markus Received on Wed Sep 17 2003 - 11:03:10 CDT