Oracle table with only current records; reduce duplicates using max(date)

I need to create a new table in oracle with only the most current date for each record (开发者_如何学编程step 1), and calculate days between (step 2).

Your suggestions are greatly appreciated:)))

Step 1: First I need to find the max (Mod_date) for each record from table USERS.

TABLE: USERS

Name................Mod_Date

Jason Martin....... 25-JUL-89

Al Mathews......... 21-MAR-89

James Smith........ 12-DEC-88

Robert Black....... 15-JAN-84

Jason Martin....... 25-JUL-99

Al Mathews......... 21-MAR-96

James Smith........ 12-DEC-98

Robert Black....... 15-JAN-94

*TABLE_DESIRED_RESULTS_step1

Name............... Max(Mod_Date)

Jason Martin....... 25-JUL-99

Al Mathews......... 21-MAR-96

James Smith........12-DEC-98

Robert Black.......15-JAN-94

Step 2: Calculate “Number of Days Between Regist_Date and Mod_Date” & add it to the table.

TABLE: REGISTRATION

Name................Regist_Date

Jason Martin.........20-JUL-99

Al Mathews...........23-MAR-96

Robert Black.........20-JAN-94

*TABLE_DESIRED_RESULTS_step2

Name...............Max(Mod_Date).....Number of Days Between Regist_Date and Mod_Date

Jason Martin...... 25-JUL-99..........5

Al Mathews........ 21-MAR-96.........-2

James Smith....... 12-DEC-98..........null

Robert Black...... 15-JAN-94..........-5

*Please note, this data is made up and I already have existing unions and joins to which I have to add this logic. Thanks and have a nice day!

---

here is my updated answer with a sample.

The important thing is that your date column have the DATE type. Here is the tables and data following your specification.

CREATE TABLE USERS
   (
    ID_USER NUMBER(6)  NOT NULL,
    NAME VARCHAR2(64)  NOT NULL,
    MOD_DATE DATE  NOT NULL,
    CONSTRAINT PK_user PRIMARY KEY (ID_USER)
   ) ;

    INSERT INTO USERS VALUES (1,'Jason Martin',TO_DATE('25-07-1989','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (2,'Al Mathews',TO_DATE('21-03-1989','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (3,'James Smith',TO_DATE('12-12-1988','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (4,'Robert Black',TO_DATE('15-01-1984','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (5,'Jason Martin',TO_DATE('25-07-1999','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (6,'Al Mathews',TO_DATE('21-03-1996','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (7,'James Smith',TO_DATE('12-12-1998','DD-MM-YYYY'));
    INSERT INTO USERS VALUES (8,'Robert Black',TO_DATE('15-01-1994','DD-MM-YYYY'));


CREATE TABLE REGISTRATION
   (
    ID_REG NUMBER(6)  NOT NULL,
    NAME VARCHAR2(64)  NOT NULL,
    REGIST_DATE DATE  NOT NULL,
    CONSTRAINT PK_reg PRIMARY KEY (ID_REG)
   ) ;

    INSERT INTO REGISTRATION VALUES (1,'Jason Martin',TO_DATE('20-07-1999','DD-MM-YYYY'));
    INSERT INTO REGISTRATION VALUES (2,'Al Mathews',TO_DATE('23-03-1996','DD-MM-YYYY'));
    INSERT INTO REGISTRATION VALUES (3,'Robert Black',TO_DATE('20-01-1994','DD-MM-YYYY'));

First step

   CREATE  TABLE TABLE_DESIRED_RESULTS_step1
   AS ( 
   SELECT
         u.NAME
        , max(u.MOD_DATE) as "maxi"
       FROM USERS u
       GROUP BY u.NAME);

second step

CREATE TABLE TABLE_DESIRED_RESULTS_step2 
AS (
SELECT 
    t.NAME 
    ,t."maxi"
    , (t."maxi" - r.REGIST_DATE ) as "Nbdays bw RegD and Mod_D"
FROM TABLE_DESIRED_RESULTS_step1 t LEFT OUTER JOIN REGISTRATION r 
ON t.NAME = r.NAME);

The trick here is that LEFT OUTER JOIN allows null value if there is no match with the join.

---

But there is a database design concern for me. If you have 2 users with the exact same name , you will merge 2 persons in one. Here a solution using IDs and doing the join on the IDs.

CREATE TABLE USERS
   (
    ID_USER NUMBER(6)  NOT NULL,
    NAME VARCHAR2(64)  NOT NULL,
    CONSTRAINT PK_user PRIMARY KEY (ID_USER)
   ) ; 

CREATE TABLE MOD_USERS
   (
    ID_MOD NUMBER(6)  NOT NULL,
    ID_USER NUMBER(6)  NOT NULL,
    CONSTRAINT PK_usermod PRIMARY KEY (ID_MOD)
   ) ; 

ALTER TABLE MOD_USERS ADD (
     CONSTRAINT FK_user_mod
          FOREIGN KEY (ID_USER)
               REFERENCES USERS (ID_USER));

CREATE TABLE REGISTRATION
   (
    ID_REG NUMBER(6)  NOT NULL,
    ID_USER VARCHAR2(64)  NOT NULL,
    REGIST_DATE DATE  NOT NULL,
    CONSTRAINT PK_reg PRIMARY KEY (ID_REG)
   ) ;

ALTER TABLE REGISTRATION ADD (
     CONSTRAINT FK_user_reg
          FOREIGN KEY (ID_USER)
               REFERENCES USERS (ID_USER))

;

First step

   CREATE  TABLE TABLE_DESIRED_RESULTS_step1
   AS ( 
   SELECT
         m.ID_USER , u.NAME
        , max(u.MOD_DATE) as "maxi"
       FROM USERS u INNER JOIN MOD_USERS m
           ON u.ID_USER = m.ID_USER
       GROUP BY m.ID_USER , u.NAME);

second step

CREATE TABLE TABLE_DESIRED_RESULTS_step2 
AS (
SELECT 
    t.ID_USER , t.NAME 
    ,t."maxi"
    , (t."maxi" - r.REGIST_DATE ) as "Nbdays bw RegD and Mod_D"
FROM TABLE_DESIRED_RESULTS_step1 t LEFT OUTER JOIN REGISTRATION r 
ON t.ID_USER = r.ID_USER);