SQL: Compare row with previous row based on a specific condition

Question

I wanted to retrieve records from a table by comparing with previous entry (for that account). Please take a look the table and data below.

In this out put I wanted is,

ID_NUM  DELIVERY_TYPE
100     2                
101     2
102     2

Explanation: I need, 100 because it is its first occurance with DELIVERY_TYPE IS 2 (Old record has 1) 101 because it is its first occurance with DELIVERY_TYPE IS 2 (Old record has 3) 102 because there is only one entry for this ID_NUM and DELIVERY_TYPE IS 2

I DON'T NEED 103 because recent DELIVERY_TYPE IS 1 even it has DELIVERY_TYPE IS 2 104 because it has two or more records with DELIVERY_TYPE IS 2

Any body knows how to achieve this result?

CREATE TABLE DEMO
  (
    ID_NUM         NUMBER(10,0),
    DELIVERY_TYPE  NUMBER(2,0),
    NAME           VARCHAR2(100),
    CREATED_DATE   DATE
  );


INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (100, 2, TO_DATE('10-FEB-12 11:08:49 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (100, 1, TO_DATE('29-JAN-12 11:09:00 AM', 'DD-MON-RR HH:MI:SS AM'));

INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (101, 2, TO_DATE('09-FEB-12 11:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (101, 3, TO_DATE('14-JAN-12 11:09:33 AM', 'DD-MON-RR HH:MI:SS AM'));

INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (102, 2, TO_DATE('02-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));

INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (103, 1, TO_DATE('01-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (103, 2, TO_DATE('02-JAN-12 11:09:33 AM', 'DD-MON-RR HH:MI:SS AM'));

INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (104, 2, TO_DATE('02-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));
INSERT INTO DEMO
  (ID_NUM, DELIVERY_TYPE, CREATED_DATE)
VALUES
  (104, 2, TO_DATE('02-FEB-12 10:09:26 AM', 'DD-MON-RR HH:MI:SS AM'));

Answer 1

use a LAG function.

it might be easier if you post a small table of values for your example instead of (/in addition to) your insert statements.