How can I extract both column from View named “Max_part_supplied’? [closed]

I have created a view named ‘Max_part_supplied’ which consists of two column as Number_of_parts and Supplier_name. I am trying to access the Supplier_name who supplies maximum number of parts, as

select MAX(Number_of_parts) as max_part, Supplier_name
from Max_part_supplied;

This is the view Max_part_supplied

Number_of_parts   Supplier_name    
1                 Ambani Traders    
3                 Lalu Traders    
2                 Paltu Traders    
1                 Sunil Traders

But I am getting error message as

Msg 8120,Level 16,State 1,line 84 Column Max_part_supplied.Supplier_name is invalid in the select list because it is not containted in either aggregate function or the GROUP BY clause.

Answer

It looks like your view already has the max per supplier, you just want the maximum of these?

select top (1) Number_of_parts, Supplier_name
from Max_part_supplied
order by Number_of_parts desc

If you want all suppliers in the event two or more have the same max, use top (1) with ties

Advertisement

Answer