Re: lesser of two dates

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 21 Jun 2007 04:48:36 -0700
Message-ID: <1182426516.211444.30430@o61g2000hsh.googlegroups.com>

On Jun 20, 9:41 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Charles Hooper wrote:
> > Two methods, using DECODE with SIGN, and using LEAST:
> > SELECT
> > C1,
> > C2,
> > DECODE(SIGN(C1-C2),1,C2,C1) L1,
> > LEAST(C1,C2) L2
> > FROM
> > T1;
>
> > C1 C2 L1 L2
> > --------- --------- --------- ---------
> > 25-JUL-07 17-MAY-07 17-MAY-07 17-MAY-07
> > 02-JUN-07 21-MAY-07 21-MAY-07 21-MAY-07
> > 31-JUL-07 28-JUL-07 28-JUL-07 28-JUL-07
> > 17-JUN-07 10-MAY-07 10-MAY-07 10-MAY-07
> > 05-JUL-07 03-AUG-07 05-JUL-07 05-JUL-07
> > 28-JUL-07 23-MAY-07 23-MAY-07 23-MAY-07
> > 01-AUG-07 17-JUN-07 17-JUN-07 17-JUN-07
> > 28-JUN-07 28-MAY-07 28-MAY-07 28-MAY-07
> > 20-MAY-07 11-JUN-07 20-MAY-07 20-MAY-07
> > 21-JUN-07 09-JUN-07 09-JUN-07 09-JUN-07
> > 26-JUL-07 24-JUL-07 24-JUL-07 24-JUL-07
> > 22-JUL-07 06-JUN-07 06-JUN-07 06-JUN-07
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Or with far less complexity:
>
> SELECT LEAST(C1,C2) FROM T1;
>
> Demo at:http://www.psoug.org/reference/number_func.html#nlst
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Yes, of the two methods that I showed, LEAST is least complex.

But, what if you want to use the obvious method, the MIN function? The DECODE(SIGN()) method can be messy when there are more than two values involved, so what about MIN? (Note: column values have changed since first run as the test table was dropped after the previous test run):
WITH T AS
(SELECT
  ROWNUM RN,
  C1,
  C2
FROM
  T1)
SELECT
  T.C1,
  T.C2,
  MIN(DV) L3
FROM
  (SELECT
    RN,
    C1 DV
  FROM
    T
  UNION ALL
  SELECT
    RN,
    C2 DV
  FROM
    T) D,
  T
WHERE
  T.RN=D.RN
GROUP BY

  T.RN,
  T.C1,
  T.C2;

C1        C2        L3
--------- --------- ---------
01-JUN-07 19-JUL-07 01-JUN-07
06-AUG-07 09-MAY-07 09-MAY-07
22-MAY-07 29-JUL-07 22-MAY-07
05-MAY-07 03-JUN-07 05-MAY-07
02-JUN-07 09-AUG-07 02-JUN-07
15-JUL-07 03-JUN-07 03-JUN-07
23-MAY-07 31-JUL-07 23-MAY-07
07-AUG-07 12-MAY-07 12-MAY-07
24-JUL-07 27-JUN-07 27-JUN-07
31-MAY-07 16-JUL-07 31-MAY-07

21-MAY-07 17-MAY-07 17-MAY-07
24-JUN-07 08-AUG-07 24-JUN-07 The above basically uses a UNION ALL between all of the C1 values and the C2 values, uses MIN to determine the lowest value per original ROWNUM and then ties the result back into the original data set. The plan:

|   1 |  HASH GROUP BY          |      |      1 |      3 |     12 |
00:00:00.01 |      21 |       |       |          |
|*  2 |   HASH JOIN             |      |      1 |      3 |     24 |
00:00:00.01 |      21 |  1011K|  1011K| 1064K (0)|
|   3 |    VIEW                 |      |      1 |     12 |     12 |
00:00:00.01 |       7 |       |       |          |
|   4 |     COUNT               |      |      1 |        |     12 |
00:00:00.01 |       7 |       |       |          |
|   5 |      TABLE ACCESS FULL  | T1   |      1 |     12 |     12 |
00:00:00.01 |       7 |       |       |          |
|   6 |    VIEW                 |      |      1 |     24 |     24 |
00:00:00.01 |      14 |       |       |          |
|   7 |     UNION-ALL           |      |      1 |        |     24 |
00:00:00.01 |      14 |       |       |          |
|   8 |      VIEW               |      |      1 |     12 |     12 |
00:00:00.01 |       7 |       |       |          |
|   9 |       COUNT             |      |      1 |        |     12 |
00:00:00.01 |       7 |       |       |          |
|  10 |        TABLE ACCESS FULL| T1   |      1 |     12 |     12 |
00:00:00.01 |       7 |       |       |          |
|  11 |      VIEW               |      |      1 |     12 |     12 |
00:00:00.01 |       7 |       |       |          |
|  12 |       COUNT             |      |      1 |        |     12 |
00:00:00.01 |       7 |       |       |          |
|  13 |        TABLE ACCESS FULL| T1   |      1 |     12 |     12 |
00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

2 - access("T"."RN"="D"."RN")

The plan using just DECODE or LEAST:

|   1 |  TABLE ACCESS FULL| T1   |      1 |     12 |     12 |
00:00:00.01 |       7 |
------------------------------------------------------------------------------------

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jun 21 2007 - 06:48:36 CDT