CODEWITHSUNDEEP
mysqlgroup-byaggregate-functions
Basel
Basel

Reputation: 1375

Aggregate functions conflict with some column in my query

I have two tables :

users:
 ___________________________
|user_id        | username  |
|_______________|___________|
|  1            |  Dolly    |
|  2            |  Didi     |
|_______________|___________|

forum:
 _____________________________________________________________
|match_static_id| comment   | timpstamp            | user_id  |
|_______________|___________|______________________|__________|
|  1            |  Hi       | 2013-07-10 12:15:03  |     2    |
|  1            |  Hello    | 2013-07-09 12:14:44  |     1    | 
|_______________|___________|______________________|__________|

this query is working fine and it uses just thw forum table:

SELECT  forum.match_static_id,
count(forum.match_static_id) 'comments_no', max(forum.timestamp)'timestamp'
FROM forum
GROUP BY forum.match_static_id
Order BY timestamp DESC

But the following query is using two tables :

SELECT  forum.match_static_id,
count(forum.match_static_id) 'comments_no', max(forum.timestamp)'timestamp', users.username
FROM forum
INNER JOIN users on users.id = forum.user_id
GROUP BY forum.match_static_id

Here I want to get the user of the max(timestamp) but i get the wrong user could any body give my a clue about this, please? Order BY timestamp DESC

Upvotes: 1

Views: 102

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Try this:

SELECT  f1.match_static_id,
  f2.comments_no, 
  f2.maxtimestamp, 
  users.username
FROM forum AS f1
INNER JOIN
(
  SELECT match_static_id, 
    max(timestamp) maxtimestamp,
    count(comment) AS comments_no
  FROM Forum
  GROUP BY match_static_id
) AS f2  ON f1.match_static_id = f2.match_static_id
        AND f1.timestamp = f2.maxtimestamp
INNER JOIN users on users.user_id = f1.user_id;

See it in action here:

Upvotes: 3

Related Questions