SQL & PL/SQL

Oracle 19.8
I have 3 tables (employees, locations, access_history) and I'm trying to get the last N (this case 2) records for each employee_id.

My expected outcome should be this(the latest 2 rows) based on access_history.access_date

EMPLOYEE_ID FIRST_NAME LAST_NAME CARD_NUM LOCATION_ID LOCATION_NAME ACCESS_DATE RN

1 Jane Smith A12345 16 Door 16 07162021 18:35:00 2
1 Jane Smith A12345 15 Door 15 07152021 18:35:00 1

2 John Doe N546830 16 Door 16 07162021 18:35:00 2
2 John Doe N546830 15 Door 15 07152021 08:25:00 1

The query I posted below partially works. It gives me the correct employee information but the location and access_history information is incorrect.

I thought a JOIN maybe needed and tried something like this but was unsuccessful.

FROM access_history a JOIN
employees e
ON a.employee_id = e.employee_id
JOIN
locations l
ON a.location_id =l.location_id

Can someone please take a look and tell me how to fix this issue. In addition, please let me know if there is a more efficient way to go about solving this problem.

I set up a very simple test case to easily reproduce the problem. Thanks in advance to all who respond.

     ALTER SESSION SET   
       NLS\_DATE\_FORMAT = 'MMDDYYYY HH24:MI:SS';  

    Create table employees(  
     employee\_id NUMBER(6),   
     first\_name VARCHAR2(20),  
     last\_name VARCHAR2(20),  
     card\_num VARCHAR2(10)  
    );  

INSERT into employees(
employee_id,
first_name,
last_name,
card_num
)
VALUES
(1,'Jane','Smith', 'A12345');

INSERT into employees(
employee_id,
first_name,
last_name,
card_num
)
VALUES
(2,'John','Doe','N546830');

     ALTER TABLE employees  
             ADD ( CONSTRAINT employees\_pk  
           PRIMARY KEY (employee\_id));  

    CREATE TABLE locations AS  
        SELECT level AS location\_id,  
      'Door ' || level AS location\_name,  

        CASE           round(dbms\_random.value(1,3))   
        WHEN 1 THEN 'A'   
        WHEN 2 THEN 'T'   
        WHEN 3 THEN 'T'   
     END AS location\_type  
    FROM   dual  
     CONNECT BY level \<= 50;  


         ALTER TABLE locations   
             ADD ( CONSTRAINT locations\_pk  
           PRIMARY KEY (location\_id));  


    create table access\_history(  
          seq\_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
           employee\_id NUMBER(6),   
           card\_num varchar2(10),  
           location\_id number(4),  
           access\_date date,  
           processed NUMBER(1) default 0  
        );  


    INSERT INTO access\_history (employee\_id, card\_num, location\_id, access\_date)  
    VALUES  
    (1, '1A2B3C', 14,  
    to\_date('20210714 18:35:00','YYYYMMDD HH24:MI:SS'));  

    INSERT INTO access\_history (employee\_id, card\_num, location\_id, access\_date)  
    VALUES  
     (1, '1A2B3C', 15,  
    to\_date('20210715 18:35:00','YYYYMMDD HH24:MI:SS'));  

    INSERT INTO access\_history (employee\_id, card\_num, location\_id, access\_date)  
    VALUES  
     (1, '1A2B3C', 16,  
     to\_date('20210716 18:35:00','YYYYMMDD HH24:MI:SS'));  

     INSERT INTO access\_history (employee\_id, card\_num, location\_id, access\_date)  
    VALUES  
    (2, 'X1234Y', 16,  
    to\_date('20210716 18:35:00','YYYYMMDD HH24:MI:SS'));  

    INSERT INTO access\_history (employee\_id, card\_num, location\_id, access\_date)  
      VALUES  
     (2, 'X1234Y', 16,  
     to\_date('20210715 08:25:10','YYYYMMDD HH24:MI:SS'));  

     INSERT INTO access\_history (employee\_id, card\_num, location\_id, access\_date)  
    VALUES  
     (2, 'X1234Y', 14,  
     to\_date('20210714 23:36:00','YYYYMMDD HH24:MI:SS'));  


     with rws as (  
      select e.employee\_id,   
                 e.first\_name,  
                 e.last\_name,  
                 e.card\_num,  
                 l.location\_id,  
                 l.location\_name,  
                 a.access\_date,  
                row\_number () over   
     (  
          partition by e.employee\_id  
           order by e.employee\_id  
      ) rn     
     FROM employees e,  
               locations l,  
                access\_history a  
    )  
     select \* from rws  
     where  rn \<= 2  
     order  by employee\_id, location\_id,  access\_date desc;