Monday, July 25, 2016

Find out the difference between the latest value and second latest value.


SELECT EVENT_TYPE, VALUE - LATEST_VALUE "Value"
  FROM (SELECT ROWNUMBER,
               EVENT_TYPE,
               VALUE,
               NVL (
                  LAG (VALUE, 1)
                     OVER (PARTITION BY EVENT_TYPE ORDER BY EVENT_TYPE, TIME),
                  0)
                  AS LATEST_VALUE,
               TIME
          FROM (  SELECT ROW_NUMBER ()
                         OVER (PARTITION BY EVENT_TYPE
                               ORDER BY EVENT_TYPE, TIME DESC)
                            AS ROWNUMBER,
                         EVENT_TYPE,
                         VALUE,
                         TIME
                    FROM EVENTS
                ORDER BY EVENT_TYPE, TIME DESC)
         WHERE ROWNUMBER < 3)
 WHERE LATEST_VALUE > 0;

Data Preparation 

CREATE TABLE EVENTS
(
  EVENT_TYPE  INTEGER                           NOT NULL,
  VALUE       INTEGER                           NOT NULL,
  TIME        TIMESTAMP(6)                      NOT NULL
);

SET DEFINE OFF;
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (2, 5, TO_TIMESTAMP('7/25/2016 4:27:27.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (2, 13, TO_TIMESTAMP('7/25/2016 4:28:44.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (2, 34, TO_TIMESTAMP('7/25/2016 4:28:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (4, 42, TO_TIMESTAMP('7/25/2016 4:27:44.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 4, TO_TIMESTAMP('7/25/2016 4:31:56.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 12, TO_TIMESTAMP('7/25/2016 4:31:53.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 34, TO_TIMESTAMP('7/25/2016 4:31:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (5, 12, TO_TIMESTAMP('7/25/2016 4:27:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
   (EVENT_TYPE, VALUE, TIME)
 VALUES
   (7, 13, TO_TIMESTAMP('7/25/2016 4:27:56.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;

1 comment:

  1. Finding the difference between the latest versions is crucial for informed decision-making. What Best Windows It ensures you're aware of new features, improvements, and potential compatibility.

    ReplyDelete