Re: Difficult Query: is this possible in SQL?

"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0407021517.1ee87cdb_at_posting.google.com...
> suppose I have the following table:
>
> CREATE TABLE (int level, color varchar, length int, width int, height
> int)
>
> It has the following rows
>
> 1, "RED", 8, 10, 12
> 2, NULL, NULL, NULL, 20
> 3, NULL, 9, 82, 25
> 4, "BLUE", NULL, 67, NULL
> 5, "GRAY", NULL NULL, NULL
>
> I want to write a query that will return me a view collapsed from
> "bottom-to-top" in order of level (level 1 is top, level 5 is bottom)
>
> So I want a query that will return
>
> GRAY, 9, 67, 25
>
> The principle is that looking from the bottom level up in each column
> we first see GRAY for color, 9 for length, 67 for width, 25 for
> height. In other words, any non-NULL row in a lower level overrides
> the value set at a higher level.
>
> Is this possible in SQL without using stored procedures?

with T as (
 select 1 id,2 a,3 b,0 c,4 d from dual
 union all
 select 2, 6,2,0,5 from dual
 union all
 select 3, 1,7,9,0 from dual
 union all
 select 4, 0,2,0,0 from dual
) select distinct

(select a from T where id=(select max(id) from T where a!=0) ),
(select b from T where id=(select max(id) from T where b!=0) ),
(select c from T where id=(select max(id) from T where c!=0) ),
(select d from T where id=(select max(id) from T where d!=0) )