MySQL GROUP BY and SORT BY with JOINS

I got 3 tables:

1. items (item_id, timestamp)
2. items_terms (item_id, term_id)
3. terms (term_id, term_name)

I need to find 5 most recent terms (term_id, term_name) based on item timestamp. I was trying to solve it like this:

SELECT t.term_id, t.term_name
 FROM items i
  INNER JOIN items_terms it USING(item_id)
  INNER JOIN terms t USING (term_id)
GROUP BY t.term_id 
ORDER BY i.timestamp DESC
LIMIT 5

But the problem is that MySQL will group items first (it will take the first term_id) and disregard ORDER BY..

I was also thinking about filtering on PHP side by removing GROUP BY and selecting more than 5 items, but this query needs to support pagination without duplicates on consecutive pages.

Will be glad to see any sug开发者_StackOverflow中文版gestions.

---

How about including the timestamp in the select statement:

SELECT t.term_id, t.term_name, MAX(i.timestamp)
 FROM items i
  INNER JOIN items_terms it USING(item_id)
  INNER JOIN terms t USING (term_id)
GROUP BY t.term_id, t.term_name
ORDER BY MAX(i.timestamp) DESC
LIMIT 5

---

I would suggest reading this article as in MySQL there are several techniques to limit rows from groups in GROUP BY select and few might suit your needs. Generally using HAVING directive with query "global" variables should be preferred as it operates on already grouped result set which positively affects performance.

EDIT: Solution would be:

SELECT DISTINCT
  t.term_id,
  t.term_name
FROM
  items i
INNER JOIN items_terms it USING(item_id)
INNER JOIN terms t USING (term_id)
ORDER BY
  i.timestamp DESC
LIMIT 5

---

 SELECT DISTINCT term_id, 
 DISTINCT term_name
 FROM(select t.term_id, t.term_name,i.timestamp FROM items i INNER JOIN items_terms it
 on i.item_id=it.item_id 
 INNER JOIN terms t
 on it.term_id=t.term_id
 GROUP BY t.term_id 
 )
 ORDER BY i.timestamp DESC
 LIMIT 5