Help with Group By and Order By

[atokatim]

I have a table that contains inbox messages between members of a site I am building. The table name is inbox. The table is laid out like this :

id | mid | fid | ofid | thetime | message | status

When a person sends a message to another person it is basically inserted like this :

INSERT INTO inbox (mid, fid, ofid, thetime, message, status) VALUES ('the_receiver_id', 'the_sender_id', 'original_id_from_first_message', '0 unread, 1 read, or 2 deleted')

What i am trying to do is show the messages on the "inbox" screen in order by last message sent to the person reviewing their inbox messages. So basically, the person looking at the screen has 4 messages from all different people. The messages should show the last posted message in the "inbox message" and the newest message should be shown at the top of the list. Kind of like how facebook has their messaging system.

What I have tried is :

$messages = mysql_query("SELECT FROM (SELECT FROM inbox WHERE mid='$mid' AND status < '3' GROUP BY fid) sorted ORDER BY sorted.thetime") or die(mysql_error());

I have read there is a mysql bug when it comes to using group by and order by together. I just can't seem to find a work around. If i need to be more detailed, I can. Any help is appreciated!!

[johanafm]

No there is no such bug. What you are referring to is most likely the effect of MySQL allowing non-standard SQL code if your db is not set to be in strict mode. The SQL standard states that if you have a group by clause, then every selected field must be either part of the group by clause or part of an aggregate function.
Assume you have a table "relatives" with

id	name
1	joe
1	jane
2	aron
2	stephen

and you

SELECT * FROM relatives
GROUP by id

In a standard compliant SQL DBMS, the query would not be allowed to execute. In MySQL, operating in non-strict mode, you will get either one of (1, joe) or (1, jane) and either one of (2, aron) and (2, stephen). That is, the result you will get is actually not predictable. Had you used an aggregate function for name (which is the only field presently not part of either the group by clause or an aggregate function), such as GROUP_CONCAT, this problem would go away.
And here's another good reason for never using * when selecting fields. If you had written "SELECT id, name" this would be obvious. Now, you'd have to look at the schema info for the table to see which fields * refers to.

SELECT id, GROUP_CONCAT(name)
FROM relatives

and you'd get

1	joe,jane
2	aron,stephen

So, the question you have to ask yourself is "Which of possibly several "thetime" do I want?". When reading your exaplanation, I understand it as if you want MAX(thetime).

So, assuming that fid is "friend id", and that you want to display the last message from each friend, and furthermore assuming you do not allow editing/updating messages, you can use either thetime or the message id, since a message sent later will have a higher id than one sent earlier.

So what you do is to first retrieve the message.id that identifies these messages

SELECT MAX(id)
FROM message
GROUP BY fid
WHERE user = @user_id

And there you have it, the information you need to retrieve the actual messages. As such

SELECT subject, message, thetime
FROM message
INNER JOIN
	(
		SELECT MAX(id) AS id
		FROM message
		GROUP BY fid
		WHERE user=@someUSer;
	) AS t
	ON t.id = message.id
WHERE user = @user_id
ORDER BY thetime

[atokatim]

That worked perfectly! I had to change a few things since there were a few mysql_errors from the code above after converting the fields to match my DB.

Here is the new code :

SELECT id, ofid, fid, message, thetime FROM inbox INNER JOIN (SELECT MAX(id) AS maxid FROM inbox WHERE mid='$mid' GROUP BY fid) AS t ON t.maxid = inbox.id WHERE mid = '$mid' ORDER BY thetime

Thank you very much!