Re: sql query - easy problem

Christoph Purrucker wrote:

>"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
>
>
>

You need to pay attention to the comment "Bad idea." The problem is your design. There is, at most, a vanishingly small number of reasons why anyone would put together a table such as you have.

Change

  PRO P DATU DATX DATY
> --- - ---- ---- ----
> aaa z 2003 2003 2000
> bbb z 2002 2001 2002
> ccc x 2003 2002 2001

To:

  PRO TYPE P DATU
> --- ---- - ----
> aaa U z 2003
> aaa X z 2003
> aaa Y z 2000
> bbb U z 2002
> bbb X z 2001
> bbb Y z 2002
> ccc U x 2003
> ccc X x 2002
> ccc Y x 2001

Think vertically not horizontally.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)