Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle Getting latest N rows from a group

BeefStuJul 17 2021

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;
This post has been answered by Solomon Yakobson on Jul 17 2021
Jump to Answer

Comments

Post Details

Added on Jul 17 2021
5 comments
80 views