categories for each data in database by a specific id and only allow get 2 for each categories mysql

Question

Possible duplicated MySql Query: Select top 3 rows from table for each category. I'm not clear with that answer that why I asked a again.

This is my database

+-------+----------+-------------+
|  id   |  app_Id  |   Content   |
+-------+----------+-------------+
|   1   |    19    | Hello Peer  |
+-------+----------+-------------+
|   2   |    20    | Hello Peer  |
+-------+----------+-------------+
|   3   |    19    | Hello Peer  |
+-------+----------+-------------+
|   4   |    19    | Hello Peer  |
+-------+----------+-------------+
|   5   |    21    | Hello Peer  |
+-------+----------+-------------+
|   6   |    20    | Hello Peer  |
+-------+----------+-------------+
|   7   |    19    | Hello Peer  |
+-------+----------+-------------+
|   8   |    20    | Hello Peer  |
+-------+----------+-------------+
|   9   |    21    | Hello Peer  |
+-------+----------+-------------+

Now how can I select the record categories by the app_Id and limit 2 for each categories and order by appId desc

If the statement above not clear.Here is my expected output record from the database above.

+-------+----------+-------------+
|  id   |  app_Id  |   Content   |
+-------+----------+-------------+
|   4   |    19    | Hello Peer  |
+-------+----------+-------------+
|   7   |    19    | Hello Peer  |
+-------+----------+-------------+
|   6   |    20    | Hello Peer  |
+-------+----------+-------------+
|   8   |    20    | Hello Peer  |
+-------+----------+-------------+
|   5   |    21    | Hello Peer  |
+-------+----------+-------------+
|   9   |    21    | Hello Peer  |
+-------+----------+-------------+

I want the query to do this any idea?

Answer 1

Try this:

SELECT id, app_Id, Content 
FROM (SELECT id, app_Id, Content, 
             IF(@appId=@appId:=app_Id, @rowNo:=@rowNo+1, @rowNo:=1) AS rowNo 
      FROM tableA, (SELECT @rowNo:=1, @appId:=0) a
      ORDER BY app_Id, id DESC
     ) AS a 
WHERE rowNo <= 2
ORDER BY app_Id

Answer 2

You can get the desired result with a simpler version as

select c.* from category c
where
(
  select count(*) from category as c1
  where c.app_Id = c1.app_Id
  and c.id < c1.id
)<=1
order by c.app_Id;