Re: Help needed: Oracle ans SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 16 Oct 2001 06:06:39 -0700
Message-ID: <9qhbcv02qln@drn.newsguy.com>

In article <3BCBFE9A.B1964A20_at_itprotect.de>, Mirko says...
>
>Hi!
>
>I've got a serious problem:
>
>I've got a table with key/value-pairs and an appropriate level. I'm
>searching for a query which results in a table that lists only the
>key/value-pairs with the greatest level.
>
>Example:
>
>key value level
>---------------------
>name alice 1
>age 13 1
>name bob 2
>name cindy 3
>gender male 1
>age 15 2
>
>The query should have the following result:
>
>key value level
>---------------------
>age 15 2
>gender male 2
>name cindy 3
>
>Can anybody help me?
>
>Mirko

sigh, never any versions. oh well.

scott_at_ORA717DEV.US.ORACLE.COM> select key, value, lvl 2 from t
3 where lvl = ( select max(lvl)

  4                     from t t2
  5                    where t2.key = t.key )

6 /

KEY VALUE LVL
---------- ---------- ----------

name       cindy               3
gender     male                1
age        15                  2

scott_at_ORA717DEV.US.ORACLE.COM>
scott_at_ORA717DEV.US.ORACLE.COM> select distinct key,

                          first_value(value) over ( partition by key
  2                                        order by lvl DESC nulls last ),
  3                       first_value(lvl) over ( partition by key
  4                                        order by lvl DESC nulls last )

5 from t
6 /

KEY FIRST_VALU
FIRST_VALUE(LVL)OVER(PARTITIONBYKEYORDERBYLVLDESCNULLSLAST) ---------- ----------

age 15 2
gender male 1
name cindy 3

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Oct 16 2001 - 08:06:39 CDT