Simple select query with mysql but can't figure out how to do it

Question

MySQL

There is such a table
id, category_id, post_id, added

You need to select for each category (catefory_id) the last 2 added (added) posts (post_id) in one request.

Answer 1

habrahabr.ru/qa/437/
isn't it?

Answer 2

There is a somewhat perverse way for aesthetes, the essence of which boils down to something like this

select * from table as a 
left join table as b on a.added<b.added and a.category_id=b.category_id
left join table as c on b.added<c.added and b.category_id=c.category_id
where b.id is not null and c.id is null 

Instead of added, you can use id, in fact it will be faster and the essence will most likely be the same, but the key on added must be required.
For all the strangeness of this select, its speed can be surprising compared to other methods, but only on small tables.
The self-evident meaning is to stretch out the records "horizontally", and then leave only those that do not have the last element, i.e. those who will be the last.

---

With all this, a much more correct way is to introduce additional. field in the table, which will have a flag indicating that this record is one of the last 2. And update this field when updating the data in the table.

Answer 3

You can also use variables.
select id, cetegory_id, post_id, added from (select *, ifnull(@b,0)<>cetegory_id as w,
@b:[email protected], @c:=cetegory_id from tbl order by cetegory_id, added desc) as x where w;
Subquery can be excluded