CODEWITHSUNDEEP
sqloracle-databaserank
user8487380
user8487380

Reputation: 156

getting other rank value than 1 in oracle in SQL query

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

Upvotes: 0

Views: 388

Answers (2)

user18098820
user18098820

Reputation:

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;

Upvotes: 2

Shahin P
Shahin P

Reputation: 367

Have you tried like this because sometimes rank() function doesn't work just outside main SQL. Try it, hope it will work.

SELECT id,
       nm,
       pd, date
FROM
  (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

Upvotes: 1

Related Questions