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!

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

John Thorton

580988 wrote:

Hi All,

I have a stored procedure which is running forever. The particular stored procedure reads a file and performs validation and inserts into some transaction table.

There are many stored procedures that are called within the main procedure. There might be locks or blocks or some query is taking long time to execute

How do i find out which query in which procedure is taking long time to execute? But sometimes the same procedures completes successfully within few seconds inserting

all the records (10000 recs).

Thanks

Gautam S

Consider to issue SQL below just prior to starting the rogue procedure.

ALTER SESSION SET SQL_TRACE=TRUE;  -- ensure the USER has necessary privilege to successfully run this SQL statement

process resultant trace file using TKPROF

ms

I just ran the main procedure now and it ran successfully within few seconds. I don't know why it was not running earlier 1 hour ago. Can someone throw some light on this?

Thanks

Gautam

John Thorton

580988 wrote:

I just ran the main procedure now and it ran successfully within few seconds. I don't know why it was not running earlier 1 hour ago. Can someone throw some light on this?

Thanks

Gautam

Something changed, but we don't know what you have.

Does any of the SQL in your mystery procedure use bind variables?

ms

No, none of the SQL use bind variables. My procedure calls the innermost procedure which is a PRAGMA AUTONOMOUS procedure.

FYI : The innermost procedure has a FOR UPDATE in a select clause. I have issued proper COMMIT/ROLLBACK also in that procedure. Does it have something to do with that? Also, the total record count i am processing is 10000.

Thanks

Gautam

ms

@John Thorton

However the records are processed successfully and inserted into relevant tables. Only the session keeps hanging in SQL Developer.

Let me know if you need anything else. If the session keeps hanging probably the application would also keep hanging in Production.

Can you someone kindly help on this.

Thanks

Gautam

ms

Hi all,

It's solved. The hanging was due to the excessive dbms_output messages being populated for each of the 10000 records.

Cookiemonster76

580988 wrote:

No, none of the SQL use bind variables.

Are you using dynamic SQL? Because unless you are, all your SQL will definitely be using bind variables. PL/SQL sorts that out automatically.

BEDE

The most simple thing is to use a log table and log there when the code gets to some place using a store procedure with pragma autonomous_transaction.

Or, you may see what shows in the sys.gv_$open_cursor and sys.gv_$sql for that session of yours, like below:

select sq.*

from sys.gv_$open_cursor oc

join sys.gv_$sql sq on oc.sql_id=sq.sql_id and oc.inst_id=sq.inst_id

where oc.sid=&sid and oc.inst_id=&inst_id

and sq.users_executing>0

;

If it's quite difficult to know which your session is, you may mark that session using the facilities of package dbms_application_info.

BEDE

If you suspect there may be some session blocking:

Select * from sys.gv_$session_blockers

;

And see what you get.

1 - 9

Post Details

Added on Jul 17 2021
5 comments
86 views