MySQL SELECT n records base on GROUP BY

Lets say I have SQL records:

Country | Nu开发者_如何学JAVAmber
USA | 300
USA | 450
USA | 500
USA | 100
UK  | 300
UK  | 400
UK  | 1000

And I am doing something like this: SELECT * FROM table GROUP BY Country.

What if, I want to choose to display the result with 2 greatest number only in each country? How can I archive this?

The result would be:

Country | Number
USA | 450
USA | 500
UK  | 400
UK  | 1000

---

Sample data

create table data (Country varchar(10), Number int);
insert into data select
'USA' , 300 union all select
'USA' , 450 union all select
'USA' , 500 union all select
'USA' , 100 union all select
'FR'  , 100 union all select
'FR'  , 420 union all select
'UK'  , 300 union all select
'UK'  , 400 union all select
'UK'  , 1000;

The first option is a pseudo rank using variables like The Scrum Meister has shown, but presented here as a single statement

SELECT Country, Number
FROM (
    SELECT
        Number,
        @r := case when @c=country then @r+1 else 1 end rownum,
        @c := Country Country 
    FROM (select @r :=0 , @c := '') x, data
    ORDER BY Country, Number DESC
) y
WHERE rownum < 3;

If you are using this in a front end, and only need 2 counts, then you can use this form that returns the counts in a list (single column)

SELECT
    Country,
    left(x,locate(',',concat(x,','),locate(',',x)+1)-1) Numbers
FROM (
    SELECT
        a.Country,
        Group_Concat(a.Number) x
    From (
        select country, number
        from data
        order by country, number desc) a
    group by a.Country
) b

The result is

"Country";"Numbers"
"FR";"420,100"
"UK";"1000,400"
"USA";"500,450"

If it is possible for ties to occur, then this variation of the 2nd form removes the ties and shows the "top 2 distinct numbers per country", as records.

SELECT distinct x.Country, x.Number
From data x
inner join
(
    SELECT
        Country,
        left(x,locate(',',concat(x,','),locate(',',x)+1)-1) Numbers
    FROM (
        SELECT
            a.Country,
            Group_Concat(a.Number) x
        From (
            select distinct country, number
            from data
            order by country, number desc) a
        group by a.Country
    ) b
) y on x.Country=y.Country
    and concat(',',y.Numbers,',') like concat('%,',x.Number,',%')
order by x.Country, x.Number Desc

Result

"Country";"Number"
"FR";"420"
"FR";"100"
"UK";"1000"
"UK";"400"
"USA";"500"
"USA";"450"

---

Since MySql does not have a built in RANK function, the query may be slow:

SET @cRank = 0;
SET @cCoutnry = '';

SELECT Country, Number
FROM (
    SELECT Number, @cRank := IF(@cCoutnry = Country, @cRank+1, 1) AS rank, @cCoutnry := Country Country 
    FROM table
    ORDER BY Country, Number DESC
) rs
WHERE rank < 3

---

Lets name your table TName, then they query would be.

SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY X.Country) AS RowNo, *
FROM (SELECT Country, Name FROM TName ORDER BY Country, Number) X ) Y WHERE Y.RowNo <= 2