relation between main table and sub table

Question

table employee

log_number	last_update_date	user id
1	2024-12-11 10:49:18.087	121
2	2024-12-12 15:49:18.087	131
2	2024-12-17 16:49:18.087	131

select *
FROM   employee e1
where  e1.last_update_date IN(
         SELECT max(e2.last_update_date)
         FROM   fusion.employee e2
         WHERE  1=1
         AND    e2.user_id = e1.user_id
         AND    e1.log_number=2.  -- E1
       )
and    e1.user_id=131

this giving output as No Data Available

select *
FROM   employee e1
where  e1.last_update_date IN(
         SELECT max(e2.last_update_date)
         FROM   fusion.employee e2
         WHERE  1=1
         AND    e2.user_id = e1.user_id
         AND    e2.log_number=2      -- E2
       )
and e1.user_id=131

this one is giving the recored result that is

log_number	last_update_date	user id
2	2024-12-17 16:49:18.087	131

could someone explain how this discrepancy is happening as we are joining the sub query with user_id.

could someone explain how this discrepancy is happening as we are joining the sub query with user_id. but in one case is giving correct result and other case it is not working. along side how the main table(E1) and sub query(E2) table gets join?

Answer 1

The first query is looking for rows where the row with the latest date out of all the rows also has log_number=2 (effectively filtering after finding the maximum).

The first query could be, equivalently, be written as:

SELECT *
FROM   (
  SELECT e1.*,
         RANK() OVER (
           PARTITION BY e1.user_id
           ORDER BY e1.last_update_date DESC
         ) AS rnk
  FROM   employee e1
  WHERE  e1.user_id=131
)
WHERE  rnk = 1
AND    log_number=2

The second query is looking for the row that has the latest date out of the rows that have log_number=2 (effectively filtering before finding the maximum).

The second query could be, equivalently, be written as:

SELECT *
FROM   (
  SELECT e1.*,
         RANK() OVER (
           PARTITION BY e1.user_id
           ORDER BY e1.last_update_date DESC
         ) AS rnk
  FROM   employee e1
  WHERE  e1.user_id=131
  AND    e1.log_number=2
)
WHERE  rnk = 1

They are looking for different things.

fiddle