For versions SQL2008 or greater you can also do this:

;WITH cteSampleData1 (matchpattern,country,rate)

AS

(

SELECT 9526,'USA',0.80 UNION ALL

SELECT 9111,'India',0.24 UNION ALL

SELECT 9111,'India',0.44 UNION ALL

SELECT 9501,'Brazil',2.21 UNION ALL

SELECT 9526,'USA',0.89 UNION ALL

SELECT 9526,'USA',0.67 UNION ALL

SELECT 9501,'Brazil',2.01

)

SELECT

r.matchpattern

,r.country

,r.MinRate

FROM

(

SELECT DISTINCT

matchpattern

,country

,MIN(rate) OVER (PARTITION BY matchpattern,country) AS MinRate

FROM

cteSampleData1

) r

ORDER BY

r.MinRate

The advantage here is you can avoid messy GROUP BY problems and easily

add multiple aggragates using different select criteria in the same select

statement such as:

;WITH cteSampleData2 (matchpattern,country,rate)

AS

(

SELECT 9526,'USA',0.80 UNION ALL

SELECT 9111,'India',0.24 UNION ALL

SELECT 9111,'India',0.44 UNION ALL

SELECT 9501,'Brazil',2.21 UNION ALL

SELECT 9526,'USA',0.89 UNION ALL

SELECT 9526,'USA',0.67 UNION ALL

SELECT 9501,'Brazil',2.01

)

SELECT

r.matchpattern

,r.country

,r.MaxRate

,r.MinRate

,r.CountryCount

FROM

(

SELECT DISTINCT

matchpattern

,country

,MAX(rate) OVER (PARTITION BY country,matchpattern) AS MaxRate

,MIN(rate) OVER (PARTITION BY matchpattern,country) AS MinRate

,COUNT(country) OVER (PARTITION BY matchpattern) AS CountryCount

FROM

cteSampleData2

) r

ORDER BY

r.MinRate