sample.sql

select observations.*, rankings.*
       from observations inner join rankings on observations.behavior = rankings.code
       where rankings.rank = (select max(rankings.rank) 
                              from observations join rankings on observations.behavior = rankings.code
                              group by brand);