SQL How select most recent records limited by one column

There is a MySql database looking like this:

Id / name / hobby / province / created - int8

I want select o开发者_StackOverflow社区nly three records for every province, but i have no idea how...

So results should be looking like this

[province1]

[1] blah bla

[2] blah blah

[3] blah blah

[province2]

[1] blah bla

[2] blah blah

[3] blah blah

etc.

Thanks for any help. Best regards. M.

---

SELECT * FROM
(
     SELECT t1.*, @num := IF(@t1 = province,@num+1,1) as num_in_group, @t1 := province
     FROM table_name t1
     INNER JOIN (SELECT @num:=0) num_table
     ORDER BY province ASC, id desc
)xxx WHERE num_in_group <=3

This is quite straightforward and not very effective approach, but it might be helpful and good to start with.

UPDATE

SELECT * FROM
(
     SELECT t1.*, @num := IF(@t1 = province,@num+1,1) as num_in_group, @t1 := province
     FROM table_name t1
     INNER JOIN (SELECT @num:=0) num_table
     INNER JOIN (SELECT @t1 := NULL)x1
     ORDER BY province ASC, id desc
)xxx WHERE num_in_group <=3

---

I don't think it can be done by one query, unless your database is small and so you can do further filtering in script.

• first, find out what provinces you have: SELECT DISTINCT province FROM t
• run a separate query for each of the provinces, sorting it by 'created DESC' and LIMIT 3.
• make sure 'province' and 'created' are indexed.

---

The "top N per group" problem is easy in databases that support row_number(), but MySQL is not one of those. There are as far as I know only fairly ugly workarounds. One approach is the group_concat way:

select  yt.province
,       lst.value
,       substring_index(
            substring_index(
                group_concat(name separator ';'),
                ';',lst.value),
            ';',-1)
from    YourTable yt
cross join
        (
        select 1 as value
        union all select 2
        union all select 3
        ) lst
group by
    yt.Province
,       lst.value;

This will return duplicate rows if there's less than three names per province. You could exclude those with another subquery.

Another approach is the variable way, as posted by Alex (which I voted for). In a slightly different format:

select  *
from    (
        SELECT  name
        ,       province
        ,       @num := if(@lastprov = province,@num+1,1) as num_in_group
        ,       @lastprov := province
        from    YourTable yt
        order by
                province
        ) as SubQueryAlias
JOIN    (SELECT @num:=0, @lastprov:=null) as DeclareVariablesSubquery
WHERE   num_in_group <= 3;

Test data:

drop table if exists YourTable;

create table YourTable (id int, name varchar(25), province varchar(25));
insert YourTable values 
    (1,'Barack','New York'), 
    (2,'George W','New York'),
    (3,'William','New York'),
    (4,'George HW','New York'),
    (5,'Ronald','Texas'),
    (6,'James','Texas'),
    (7,'Gerald','Texas'),
    (8,'Richard','Texas'),
    (9,'Lyndon','California'),
    (10,'John','California'),
    (11,'Dwight','Rhode Island'),
    (12,'Harry','Wisconsin');

---

This won't work for MySQL (which is why I marked this answer as community wiki), but it's interesting for people using a different DBMS and stumbling upon this question :

WITH TT (id, province, R) AS (
    SELECT id, province, row_number() OVER(PARTITION BY province ORDER BY id DESC)
      FROM your_table
  GROUP BY id, province
)
SELECT id, province
  FROM TT
 WHERE R <= 3;