Re: Difficult Query: is this possible in SQL?

robertbrown1971_at_yahoo.com (Robert Brown) 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?
>
>
>
> Thanks!
>
>
> - Robert

SELECT * FROM
(
select ROW_NUMBER() OVER (ORDER BY L DESC) RN,

         first_value(color) over ( order by case when color is null then -1 else rownum end desc),

         first_value(length) over ( order by case when length is null then -1 else rownum end desc),

         first_value(width) over ( order by case when width is null then -1 else rownum end desc),

         first_value(hgt) over ( order by case when hgt is null then -1 else rownum end desc)
from ( SELECT * FROM foobar ORDER BY L) )
WHERE RN = 1 Received on Tue Jul 06 2004 - 00:10:47 CDT