MySQL query - problem with order by

Question

I'm using two tables for selecting messages between users. Table "messages" for recording the messages and table "members" for checking users data (are they activ or deleted).

This query working fine.

What I need to do is list the received messsages order by last received. I tried to do with "ORDER BY messages.id DESC" at the end of this query but it didn't work. All messages are listed from first received.

This is the mysql join table query that I'm using:

sql = "SELECT DISTINCT messages.fromid,
            messages.readed,
            messages.fromid,
            messages.toid  ,
            members.id AS pid
FROM            messages
INNER JOIN members
ON              members.id = messages.fromid
WHERE           messages.toid              =  ".$mid."
AND             members.status             = 7
AND             messages.kreaded          !='1'
AND             messages.subject          != 'readed'
GROUP BY        fromid"

Is there any way to do this?

Answer 1

Do you have a column in the table for "received" or a column with some sort of timestamp? If you do, you should order by that.

If not, please post the entire table structure so we can better help you.

Answer 2

How about sonething like

SELECT DISTINCT messages.fromid, 
            messages.readed, 
            messages.fromid, 
            messages.toid  , 
            members.id AS pid 
FROM            messages 
INNER JOIN members 
ON              members.id = messages.fromid 
WHERE           messages.toid              =  ".$mid." 
AND             members.status             = 7 
AND             messages.kreaded          !='1' 
AND             messages.subject          != 'readed'
ORDER BY    messages.fromid ASC,
            messages.id DESC

OR

SELECT DISTINCT messages.fromid, 
            messages.readed, 
            messages.fromid, 
            messages.toid  , 
            members.id AS pid 
FROM            messages 
INNER JOIN members 
ON              members.id = messages.fromid 
WHERE           messages.toid              =  ".$mid." 
AND             members.status             = 7 
AND             messages.kreaded          !='1' 
AND             messages.subject          != 'readed'
ORDER BY    messages.fromid ASC,
            messages.sendtime DESC

In messages.fromid ASC the ASC is purely syntactically, as ORDER BY without specifying ASC/DESC assumes ASC.

SELECT *
FROM TABLE
ORDER BY COL ASC

is the same as

SELECT *
FROM TABLE
ORDER BY COL

Answer 3

I'm no sql expert, by when you tried your ORDER BY clause, did you order by messages.id or the alias "pid" you declared in your statement? Might that have an effect on the order by not working as expected?