Select Min Values and Max Count

[chamilz]

Hi,

I have a data table like below.

From this table I want to "select the STORE that has highest item count AND minimum AvgScore".

Store Item AvgScore
122 A001 10
122 A002 10
122 A007 10
121 A002 11
121 A001 11
121 A007 11
104 A007 5
104 A001 5
102 A002 7
102 A001 7

Result should be:

Store: 122
As it has 3 items and minimum AvgScore,10.

Thanks!

 

[Geoka]

Select Top 1 Store, count(Item) Item, min(avgscore) avgscore from Stock group by store
Order by Item Desc, avgscore Asc

 

[chamilz]

Thanks Geoka!!