oracle11ginner-joinoracle-analytics

How do I use rank() with an inner join?


I want to join my CHEESE table with FRESHNESS to get CHEESE and FRESHNESS code for cheeses where max(seq_no) for each cheese id is MOLD.

When using rank(), where do I join to FRESHNESS?

CHEESE                                    FRESHNESS
CHEESE_ID  SEQ_NO  FRESH_CODE             FRESH_CODE   FRESH_DESC      
=================================         ========================
  1         1        MOLD                 MOLD         MOLDY CHEESE    
  1        23        FRSH                 FRSH         EDIBLE
  1        34        FRSH
  2         2        FRSH
  2        18        MOLD
  3         3        MOLD
  3         5        MOLD
  3         7        MOLD


 DESIRED RESULT
 ==========================
 CHEESE_ID  SEQ_NO  FRESH_CODE  FRESH_DESC      SEQ_RANK
 2           18     MOLD        MOLDY CHEESE    1
 3            7     MOLD        MOLDY CHEESE    1

Here's my code I'm using to get the desired sequence numbers.

select 
       cheese_id,seq_no,fresh_code,seq_rank
  from ( select 
         cheese_id,seq_no, fresh_code, 
         rank() over (partition by cheese_id
                          order by seq_no desc) seq_rank
from cheese
where seq_rank = 1
 and  fresh_code = 'MOLD'

Solution

  • You can either do the join in the subquery

    select cheese_id,seq_no,fresh_code,fresh_desc,seq_rank
      from ( select cheese_id,
                    seq_no, 
                    fresh_code, 
                    fresh_desc,
                    rank() over (partition by cheese_id
                                     order by seq_no desc) seq_rank
              from cheese
                   join freshness using (fresh_code) )
     where seq_rank = 1
       and fresh_code = 'MOLD'
    

    or you can join to your subquery

    select cheese_id,seq_no,fresh_code,fresh_desc,seq_rank
      from ( select cheese_id,
                    seq_no, 
                    fresh_code, 
                    fresh_desc,
                    rank() over (partition by cheese_id
                                     order by seq_no desc) seq_rank
              from cheese ) cheese_outer
           join freshness using (fresh_code)
     where seq_rank = 1
       and fresh_code = 'MOLD'