GROUP BY, ORDER BY - How to make group by consider latest apperance of item

Question

I have a query like this (Mysql 5.X, PHP - formatted for legibility)

$query ="
SELECT 
  p.p_pid, 
  p.p_name, 
  p.p_url 
FROM 
  activity a, 
  products p 
WHERE 
  a.a_uid= ".$uid_int." 
  AND a.a_pid > 0 
  AND p.p_pid = a.a_pid 
GROUP BY 
  a.a_pid 
ORDER BY 
  a.a_time DESC LIMIT 6
");

In general it should produce a unique list of the 6 latest products the user has seen.

The problem is that if the user has seen a product more than once. one of them in the last 6 activities and one of them before the latest 6 activities the query does not return the product. I assume that the (group by) does not leave a_time with the latest time of apperance of the product. How can I correct it?

Answer 1

Have you tried ordering by MAX(a.a_time) ?

SELECT 
  p.p_pid, 
  p.p_name, 
  p.p_url 
FROM products p 
INNER JOIN activity a on p.p_pid = a.a_pid 
WHERE 
  a.a_uid= ".$uid_int." 
GROUP BY 
  p_pid, p_name, p_url
ORDER BY 
  max(a.a_time) DESC 
  LIMIT 6

As a best practice, use GROUP BY on every column you use without an aggregate. MySQL is one of the few databases that allow you to use a column that's not being grouped on. It'll give you a random column from the rows you selected.

Answer 2

I sure hope that $uid_int variable is double checked for SQL injection.

$query ="
SELECT 
  MAX(p.p_pid)  p_pid, 
  MAX(p.p_name) p_name, 
  MAX(p.p_url)  p_url
FROM 
  activity a
  INNER JOIN products p ON p.p_pid = a.a_pid 
WHERE 
  a.a_uid= ".$uid_int." 
  AND a.a_pid > 0 
GROUP BY 
  a.a_pid 
ORDER BY 
  MAX(a.a_time) DESC 
LIMIT 6
");

Sometimes I wonder if it was a good design decision from MySQL to allow grouping without explicit aggregation...