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'

• oracle11g
• inner-join
• oracle-analytics

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'