Comparison Clarification I would to hear the solution for the below requirement. 

I have table A with key number and Fruits, table B with Fruits and rating with the below data. I have to retrieve result with least rating from B mapping keynumber A by based on fruits. 

My result should be like 

Keynumber   Fruits  Rating
123         Orange  4
456         Apricot 2


Table A

Key number  Country
123         Apple
123         Orange
456         Cherry
456         Apricot


Table B

Country Rating
Apple   5
Orange  4
Cherry  8
Apricot 2


I used the below query but it is displaying the two records of the same keynumber

select A."key number", A.fruits, B.fruits, B."rating from"
table A 
  left join table B on A.fruits = B.fruits 
order by a."key number"


Keynumber   Country Rating
123 Orange  4
123 Apple   5
456 Apricot 2
456 Cherry  8
declare @a table (Key_number int,  Country varchar(100));
insert into @a values
(123,         'Apple'),
(123,         'Orange'),
(456,         'Cherry'),
(456,         'Apricot');

declare @b table (Country varchar(100), Rating int);
insert into @b values
('Apple',   5),
('Orange',  4),
('Cherry',  8),
('Apricot', 2);

select top(1) with ties a.*, b.Rating
from @a a join @b b
        on a.Country = b.Country
order by row_number() over(partition by a.Key_number order by b.Rating);