CODEWITHSUNDEEP
sqldatabaseoracle-database
luhfluh
luhfluh

Reputation: 484

Return max date from multiple tables join oracle

I have 4 tables with the following relevant information I want to retrieve.

Table: Staff_profile (STAFF_ID, STAFF_USERNAME, STAFF_NAME, STAFF_JOB_ID, STAFF_FACULTY_ID, STAFF_OFF_TEL, STAFF_EMAIL) - holds staff information

Table: RFMUSERHISTORY (uh_staff_id, UH_DATETIME) - holds login history

Table: RFMUSERROLEJOBMAP (role_id, job_id ) - maps role-2-job [this is because job table pre-exists and this new app is only picking certain job ids to use against its own roles table

Table: RFMUSERROLE (USERROLE_CODE, USERROLE_ID) - holds user roles information

Now I want to get the last login (max date for that user in userhistory) details including role and staff details for any particular person who logs in. I have had trouble with my code and finally just resorted to selecting all the records for that user with the UH_datetime ordered desc so I can pick that latest topmost record.

Here is my current code (very inefficient as described above):

SELECT a.STAFF_ID, a.STAFF_USERNAME, a.STAFF_NAME, a.STAFF_JOB_ID, a.STAFF_FACULTY_ID, 
    a.STAFF_OFF_TEL, a.STAFF_EMAIL, to_CHAR(b.UH_DATETIME,'Dy DD-MM-YYYY HH24:MI:SS') 
    AS UH_DATETIME, e.USERROLE_CODE, e.USERROLE_ID  
        FROM STAFF_PROFILE a  
        LEFT JOIN RFMUSERHISTORY b ON STAFF_ID=b.uh_staff_id 
        LEFT JOIN RFMUSERROLEJOBMAP d ON a.STAFF_JOB_ID=d.job_id  
        LEFT JOIN RFMUSERROLE e ON d.role_id=e.userrole_id 
        WHERE STAFF_ID=:eid1 ORDER BY b.UH_DATETIME DESC

Upvotes: 0

Views: 3030

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

You could use an analytic function to rank the rows and then select the most recent one. If you're really just selecting the data for a single STAFF_ID, this is probably no more efficient than nesting your original query in an outer query that selects the row using a ROWNUM predicate. If you are selecting the data for multiple staff members, however, this should be more efficient.

SELECT *
  FROM (
    SELECT a.STAFF_ID, 
           a.STAFF_USERNAME, 
           a.STAFF_NAME, 
           a.STAFF_JOB_ID, 
           a.STAFF_FACULTY_ID, 
           a.STAFF_OFF_TEL, 
           a.STAFF_EMAIL, 
           to_CHAR(b.UH_DATETIME,'Dy DD-MM-YYYY HH24:MI:SS') AS UH_DATETIME, 
           e.USERROLE_CODE, 
           e.USERROLE_ID,
           dense_rank() over (partition by a.staff_id order by b.uh_datetime desc) rnk
      FROM STAFF_PROFILE a  
            LEFT JOIN RFMUSERHISTORY b ON STAFF_ID=b.uh_staff_id 
            LEFT JOIN RFMUSERROLEJOBMAP d ON a.STAFF_JOB_ID=d.job_id  
            LEFT JOIN RFMUSERROLE e ON d.role_id=e.userrole_id 
      WHERE STAFF_ID=:eid1 
    )
 WHERE rnk = 1

Upvotes: 3

Andomar
Andomar

Reputation: 238068

Oracle doesn't send rows over the network before you ask for them. If your client code only request the first row, your query should be efficient enough.

Another option is to limit Oracle to one row with rownum:

where rownum = 1

Upvotes: 1

Related Questions