mysql get latest records by order by desc without subquery


[COLOR=#00008B]SELECT * [COLOR=#00008B]FROM customer e 
[COLOR=#00008B]WHERE e.id=324 [COLOR=#00008B]AND e.g_id [COLOR=#00008B]IN('x133fv','be6544','e992170','93611c') 
[COLOR=#00008B]and e.enrol_id = ( [COLOR=#00008B]select e1.enrol_id [COLOR=#00008B]from customer e1 [COLOR=#00008B]WHERE e1.id=324 [COLOR=#00008B]AND e1.g_id=e.g_id [COLOR=#00008B]ORDER [COLOR=#00008B]BY update_time [COLOR=#00008B]DESC, posted_time [COLOR=#00008B]DESC, enrol_id [COLOR=#00008B]DESC LIMIT 1 ) 


Is enrol_id unique in the table? If not, then the query could potentially return multiple rows for a given (g_id, id) combination, with the same enrol_id but different update_time and posted_time, which would contradict the effort of the ORDER BY.

Without fully understanding the business logic, one alternative to the query using JOIN could be something like:

select e.*
from customer e,
(select e1.g_id, e1.enrol_id
from customer e1
where e1.id=324 
and e1.g_id IN('x133fv','be6544','e992170','93611c')
group by e1.g_id
order by e1.update_time DESC, e1.posted_time DESC, e1.enrol_id DESC
limit 1) t
where e.id = 324
and e.g_id = t.g_id
and e.enrol_id = t.enrol_id;

Yes enrol_id is unique.

The query you gave dint quite work good. I tried this way though,

select e.* from customer e, (select e1.g_id, e1.enrol_id from customer e1 where e1.id=324 and e1.g_id IN(‘x133fv’,‘be6544’,‘e992170’,‘93611c’) order by e1.update_time DESC, e1.posted_time DESC, e1.enrol_id DESC) t where e.id = 324 and e.g_id = t.g_id and e.enrol_id = t.enrol_id group by e.g_id, e.id;
Now my question here is question here is : Will the inner sort guarantee that i get latest record since im doing a outer group by clause. Will anytime mysql change it algo to sort differently inner query join with outer group by.