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;