SQL to get highest value

[mrchonginhk]

I have a table1 like this

Country/Product/US$
===================
UK/A/200
UK/B/300
UK/C/100
US/A/200
US/B/100
US/C/500

I want a SQL to select the max sales for each country with associated product. Desired output is:-

Country/Product/US$
===================
UK/B/300
US/C/500

What should be the SQL for this ? Thanks

 

[krishnaoptif]

Hi,

find below SQL...for the same..

select country, max(US) as US into #Temp_1 from table1
group by country
select Table1.country,Table1.Product, Table1.US from Table1
inner join #Temp_1 on Table1.Country =
#Temp_1.Country
and Table1.US = #Temp_1.US



OR



select Table1.country,Table1.Product, Table1.US from Table1
inner join (select country, max(US) as US from table1 group by country) as Tmp_Tbl
on Table1.Country = Tmp_Tbl.Country
and Table1.US = Tmp_Tbl.US





Regards,
Krishna