Re: SQL min value

From: Dmitry E. Loginov <dmitry_loginov_at_mtu.ru>
Date: Wed, 21 Nov 2001 00:50:43 +0300
Message-ID: <9tej98$1gnc$1@gavrilo.mtu.ru>

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Nov 21 00:48:58 2001

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL>
SQL> DROP TABLE T1 CASCADE CONSTRAINTS ; Table dropped.

SQL>
SQL> CREATE TABLE T1   2 OBJ NUMBER,
  3 V1 NUMBER,
  4 V2 NUMBER); Table created.

SQL>
SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 1, 1, 1);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 1, 2, 2);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 1, 3, 5);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 2, 1, 6);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 2, 2, 7);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 2, 3, 5);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 2, 3, 5);

1 row created.

SQL> INSERT INTO T1 ( OBJ, V1, V2 ) VALUES   2 2, 4, 5);

1 row created.

SQL>
SQL> select * from T1;

       OBJ V1 V2
---------- ---------- ----------

         1          1          1
         1          2          2
         1          3          5
         2          1          6
         2          2          7
         2          3          5
         2          3          5
         2          4          5

8 rows selected.

SQL>
SQL> -- all records that corresponds your clause
SQL> select *

  2 from T1 where (OBJ,V2) in (select OBJ,min(V2) from T1 group by OBJ);

       OBJ V1 V2
---------- ---------- ----------

         1          1          1
         2          3          5
         2          4          5
         2          3          5

SQL>

SQL> -- Distinct sets of the values that corresponds your clause SQL> select B.OBJ,B.V1,B.V2
  2 from T1 A, T1 B
  3 where B.OBJ=A.OBJ
  4 having MIN(A.V2)=B.V2
  5 group by B.OBJ,B.V1,B.V2;

       OBJ V1 V2
---------- ---------- ----------

         1          1          1
         2          3          5
         2          4          5

SQL>
"gil guerillot" <gil.guerillot_at_ratp.fr> wrote in message news:9tb77m$1nc2$1_at_gotix.reseau.ratp...
> table with 3 fields OBJ,V1,V2
>
>
> I need sql select
> how to find for each OBJ,the V1 value corresponding at the record where V2
> is the smaller?
>
>
Received on Tue Nov 20 2001 - 15:50:43 CST