getting other rank value than 1 in oracle in SQL query

Question

There is 1 SQL query when i used like below-

select * from
(select a.id, a.nm, b.pd, b.date, rank() over(partition by a.id order by b.date desc) rnk 
 from tab1 a, tab2 b where a.id = b.id) 
where rnk =1 

then getting output like below-

id    nm    pd    date         rnk
--    ---   ---   ----------   ---
11    abc   a15   11/04/2022    1
11    abc   a15   11/04/2022    1
14    gef   a23   11/04/2022    1
14    gef   a23   10/04/2022    12
19    lfb   a37   11/04/2022    1
19    lfb   a37   08/04/2022    21

But I want only one value to be select as order by latest date. Could you please help me on this to resolve it to select one value for 1 id like below-

id    nm    pd    date         rnk
--    ---   ---   ----------   ---
11    abc   a15   11/04/2022    1
14    gef   a23   11/04/2022    1
19    lfb   a37   11/04/2022    1

Answer 1

You need to specify a second column in the order by of the RANK(), so that there are no duplicate pairs of values (e.g. b.id). I've also normalised the JOIN operation.

select * from
  (select 
     a.id, 
     a.nm, 
     b.pd, 
     b.date, 
     rank() over (
        partition by a.id 
        order by b.[date] desc, b.id asc
        ) as rnk 
  from tab1 a
  join tab2 b on a.id = b.id
  ) s
where rnk = 1;