Re: Is this possible in SQL?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 20 Jul 2002 08:31:52 -0700
Message-ID: <ahbvp801et2@drn.newsguy.com>

In article <44a19320.0207192257.1bf5511b_at_posting.google.com>, tedchyn_at_yahoo.com says...
>
>dreamer33334_at_yahoo.com (Joe Imbrogo) wrote in message
>news:<65dcbf17.0207191718.1923e6be_at_posting.google.com>...
>> Let us assume there is a view A with data as follows...
>>
>> X Y Z
>> -----------------------
>> 12 2 2
>> 14 2 3
>> 17 2 9
>> 19 3 12
>> 21 3 16
>> 25 4 21
>>
>> I need get the value of X for every unique value of Y, where Z is the
>> lowest in its range.
>>
>> For example, the result for the above example would be
>>
>> X Y
>> -------------
>> 12 2
>> 19 3
>> 25 4
>>
>> I know this is very easy to do in PL/SQL, but I wanted to take up the
>> challenge of doing this is a regular SQL statment. However, I could
>> not make much progress.
>>
>> Any help on this would be greatly appreciated.
>>
>> Thanks,
>> Joe
>
>
>joe, here it is
>
>SQL> select * from test;
>
> C1 C2 C3
>---------- ---------- ----------
> 12 2 2
> 14 2 3
> 17 2 9
> 19 3 1
> 21 3 16
> 25 4 21
>
>6 rows selected.
> 1 select min( c1),c2
> 2 from test x
> 3* group by c2
>QL> /
>
> MIN(C1) C2
>--------- ----------
> 12 2
> 19 3
> 25 4
>ted

No - that gets the MIN(X) by Y, they wanted the X that goes with the MIN(Z) by Y.

So, consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select y, min(x)   2 from t
  3 group by y
  4 /

         Y MIN(X)
---------- ----------

         2         12
         3         19
         4         25

That matches what they ask for but only cause the min(x) and min(z)'s were "paired". If we change the data just a tad:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update t set z = decode( z, 9, 2, 2, 9 ) where z in (9,2);

2 rows updated.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t;

         X Y Z
---------- ---------- ----------

        12          2          9
        14          2          3
        17          2          2  <<<=====
        19          3          1  <<<=====
        21          3         16
        25          4         21  <<<=====

6 rows selected.

those are the rows they wanted...

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select y, min(x)   2 from t
  3 group by y
  4 /

         Y MIN(X)
---------- ----------

         2         12
         3         19
         4         25

So, we can use a trick (if Z is a positive number, gets trickier if it can go negative)

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select y, to_number( substr( data, 5 ) ) x   2 from (
  3 select y, min( to_char(z,'fm0009') || x ) data   4 from t
  5 group by y
  6 )
  7 /

         Y X
---------- ----------

         2         17
         3         19
         4         25

Or in a more straightforward sense with analytic functions:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select distinct y, first_value(x) over (partition by y order by z)
  2 from t
  3 /

         Y FIRST_VALUE(X)OVER(PARTITIONBYYORDERBYZ)

---------- ----------------------------------------
         2                                       17
         3                                       19
         4                                       25

Or even:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select y, x   2 from t
  3 where (y,z) in ( select y, min(z)

  4                      from t
  5                     group by y )

  6 /

         Y X
---------- ----------

         2         17
         3         19
         4         25

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select y, x   2 from t
  3 where z = (select min(z)

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

  6 /

         Y X
---------- ----------

         2         17
         3         19
         4         25

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

--
Thomas Kyte (tkyte@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 Sat Jul 20 2002 - 10:31:52 CDT