MySQL GROUP BY and SORT BY with JOINS
I got 3 tables:
- items (item_id, timestamp)
- items_terms (item_id, term_id)
- 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
精彩评论