getting other rank value than 1 in oracle in SQL query-CodePudding

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

CodePudding user response：

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

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;

CodePudding user response：

You can use a MAX function over the date field instead of using a window function subquery:

SELECT 
    a.id,
    a.nm, 
    b.pd,
    MAX(b.date) as date
FROM 
    tab1 a 
INNER JOIN 
    tab2 b 
ON 
    a.id = b.id
GROUP BY 
    a.id,
    a.nm, 
    b.pd

Note: I've also explicitly written your hidden inner join.