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'));
You can use the ROW_NUMBER() function to isolate the most recent rows by partitioning on the ID_NUM and ordering by the CREATED_DATE descending. Then identify the occurrences of more than one DELIVERY_TYPE = 2 to filter the result set:
SELECT ID_NUM, DELIVERY_TYPE
FROM (SELECT ID_NUM, DELIVERY_TYPE,
ROW_NUMBER() OVER (PARTITION BY ID_NUM
ORDER BY CREATED_DATE DESC) AS RN
FROM DEMO)
WHERE RN = 1
AND DELIVERY_TYPE = 2
MINUS
SELECT ID_NUM, DELIVERY_TYPE
FROM (SELECT ID_NUM, DELIVERY_TYPE, COUNT(*) AS REC_COUNT
FROM DEMO
WHERE DELIVERY_TYPE = 2
GROUP BY ID_NUM, DELIVERY_TYPE
HAVING COUNT(*) > 1)
This will return the expected results.