Re: sql query - easy problem

From: Jan <janik_at_pobox.sk>
Date: 17 Sep 2003 08:54:21 -0700
Message-ID: <81511301.0309170754.f3adcb@posting.google.com>

didn`t test it,
but if you have Oracle 8i and up then that the way...

SELECT product,

       type,
       date

  FROM
(SELECT product,

       type,
       date,
       ROW_NUMBER()OVER(PARTTION BY product ORDER BY date DESC) row_num

 FROM product_table)
  WHERE row_num=1

"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
Received on Wed Sep 17 2003 - 10:54:21 CDT