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