Re: Is this possible in SQL?

From: Marc Blum <marc_at_marcblum.de>
Date: Sat, 20 Jul 2002 10:39:44 +0200
Message-ID: <p88ijusumh5ju0cja8jcrcs673kh9ecddr@4ax.com>

On 19 Jul 2002 18:18:30 -0700, dreamer33334_at_yahoo.com (Joe Imbrogo) wrote:

>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

Another solution, using an Analytical Function, introduced in 8.1.6. Only available in Enterprise Edition. In 9 also available in Standard edition. If your table is big, you get a real benefit from scanning the table only once.

SQL> DROP TABLE t;

Table dropped.

SQL> CREATE TABLE t
  2 (x NUMBER, y NUMBER, z NUMBER);

Table created.

SQL>
SQL> INSERT INTO t VALUES( 12, 2, 2);

1 row created.

SQL> INSERT INTO t VALUES( 14, 2, 3);

1 row created.

SQL> INSERT INTO t VALUES( 17, 2, 9);

1 row created.

SQL> INSERT INTO t VALUES( 19, 3, 12);

1 row created.

SQL> INSERT INTO t VALUES( 21, 3, 16);

1 row created.

SQL> INSERT INTO t VALUES( 25, 4, 21);

1 row created.

SQL> COMMIT; Commit complete.

SQL>
SQL> SELECT * FROM t;

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

        12          2          2
        14          2          3
        17          2          9
        19          3         12
        21          3         16
        25          4         21

6 rows selected.

SQL>
SQL> SELECT x,y,z,

  2         row_number() 
  3            over (PARTITION BY y 
  4                  ORDER BY z ASC) AS rn 

  5 FROM t;

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

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

6 rows selected.

SQL> SELECT x,y
  2 FROM (
  3 SELECT x,y,z,

  4         row_number() 
  5            over (PARTITION BY y 
  6                  ORDER BY z ASC) AS rn 

  7 FROM t) v
  8 WHERE rn = 1;

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

        12          2
        19          3
        25          4

SQL>
SQL> DROP TABLE t;

Table dropped.

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Jul 20 2002 - 03:39:44 CDT