Select MAX(ID) mysql

Question

I want to group mysql record with idorig and select the max id in order to select latest record.

My database content is like that:

ID     | IDORIG     | MESSAGE    | STATUS
100    | 100        | azerty     | 2
101    | 100        | azerty     | 1
102    | 100        | azerty     | 1
103    | 100        | azerty     | 0
104    | 104        | azerty     | 0
105    | 104        | azerty     | 0
106    | 104        | azerty     | 0
107    | 104        | azerty     | 0
108    | 104        | azerty     | 0

My SQL request is:

SELECT MAX(id),message,idorig,status FROM messages GROUP BY idorig order by id desc

The sql returns the good id: 102

But problem is that if I try to return status I got 2 not 0

How can I change sql request to get the latest id with group idorig and even get the records of 102 not 100

Answer 1

The error takes place bacause MySQL doesn't know which status from grouped records should results you. It knows that you want max(id) from group but according status is not obvious. Some SQL engines would tigger exception but not MySQL. It returns first record from group (status=2 in this case), I guess. You can do it this way

Select id, idorig, message, status where id in (SELECT MAX(id) FROM messages GROUP BY idorig)

MySQL is going to get Max(id) for each group (which is unique) then use it in parent query to select interesting data from this particulars records.

Answer 2

select ID,idorig,Message,Status from messages order by ID desc limit 1;

or

Select ID,idorig,Message,Status from messages where ID = (select max(ID) from messages);