MySQL Select the last 3 Records per Group

问题:

I have 2 table news and news_category they can join on news.category_id = news_category.id

I want to get all of the news_category rows and get 3 last rows of news for each category id

---

thanksstrong text for help

回答1:

Baron Schwartz has well explained this problem in LINK

Below is based on his method. You can try it yourself in db-fiddle. db-fiddle, I created a simplified schema to show it.

set @num := 0, @category_id := '';

select t.id, t.title
from (
  select category_id, id, title,
        @num := if(@category_id = category_id, @num + 1, 1) as row_number,
        @category_id := category_id as dummy
  from news
  group by category_id, id, title
) t
where t.row_number <= 3

Update

As mentioned by Gordon Linoff below, this answer is not guaranteed to work. This is also well explained in the referenced blog.

回答2:

Unfortunately, Jacob's answer is not guaranteed to work, regardless of what the reference says. The problem is that MySQL does not guarantee the order of evaluation of expressions in a SELECT. Hence, you should not be assigning a variable in one expression and then using it in another. The behavior can change.

In addition, more recent versions of MySQL are more finicky when using variables with group by and order by, so an additional subquery is needed.

The query does not need a group by. You should be doing this with order by:

select n.id, n.title
from (select n.*,
             (@rn := if(@c = category_id, @rn + 1,
                        if(@c := category_id, 1, 1)
                       )
             ) as rn
      from (select n.*
            from news n
            order by n.category_id, n.id desc, n.title
           ) n cross join
           (select @rn := 0, @c := -1) params
     ) n
where n.rn <= 3;