Get most recent data from joined table with condition-CodePudding

I have this query:

SELECT V2.EMPID, V2.PDATE1, T3.JOB_POSITION_NM
  FROM USER_VIEW V2
       JOIN
       (SELECT EMPID, JOB_POSITION_NM, EFFDT
          FROM (  SELECT A.EMPID, B.JOB_POSITION_NM, A.EFFDT
                    FROM T_EMP A
                         JOIN T_JOB_MASTER B
                            ON A.JOB_POSITION = B.JOB_POSITION
                ORDER BY EFFDT DESC)) T3
          ON     T3.EMPID = V2.EMPID
             AND TO_CHAR (T3.EFFDT, 'YYYY-MM-DD') < TO_CHAR (V2.P_DATE1, 'YYYY-MM-DD')

Here are the sample data:

User_View V2

empid | pdate1
--------------
EM001 | 2021-12-01

T3

empid | job_position_nm | effdt
--------------------------------
EM001 | Operator        | 2016-06-12
EM001 | Sub Team Leader | 2018-10-01
EM001 | Team Leader 1   | 2020-07-01
EM001 | Team Leader 2   | 2022-01-03

but since the T3 query had multiple data for EFFDT that less than P_DATE1 it returns multiple rows on result. I want something like WHERE ROWNUM = 1 on T3 to showing single result on the query.

Any workaround to implement similar behavior? Thanks

CodePudding user response：

How about simplifying it a little bit, using a CTE along with row_number analytic function (could've been rank, but - it seems you want to get at most one row as a result).

Also, compare dates, don't convert them to strings and then compare those strings. Why would you want to do that?

with temp as
  (select a.empid, b.job_position_nm, a.effdt,
     row_number() over (order by a.effdt desc) rn
   from t_emp a join t_job_master b on a.job_position = b.job_position
  )
select v2.empid, v2.pdate1, t.job_position_nm
from user_view v2 join temp t on t.empid = v2.empid
  and t.effdt < v2.p_date1
  and t.rn = 1;