# mysql get latest records by order by desc without subquery

**URL:** <https://forums.percona.com/t/mysql-get-latest-records-by-order-by-desc-without-subquery/3675>\
**Category:** Other MySQL® Questions\
**Created:** [August 13, 2014, 12:37am UTC](https://forums.percona.com/t/mysql-get-latest-records-by-order-by-desc-without-subquery/3675 "2014-08-13T00:37:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![infdude666](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/infdude666/32/11425_2.png) [@infdude666](https://forums.percona.com/u/infdude666)\
**Post date:** [August 13, 2014, 12:37am UTC](https://forums.percona.com/t/mysql-get-latest-records-by-order-by-desc-without-subquery/3675/1 "2014-08-13T00:37:37Z")

</div>

```auto

[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 ) 

```

---

<div class="post-metadata">

**Author:** ![psong](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/psong/32/16894_2.png) [@psong](https://forums.percona.com/u/psong)\
**Post date:** [August 13, 2014, 5:03am UTC](https://forums.percona.com/t/mysql-get-latest-records-by-order-by-desc-without-subquery/3675/2 "2014-08-13T05:03:06Z")

</div>

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:

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![infdude666](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/infdude666/32/11425_2.png) [@infdude666](https://forums.percona.com/u/infdude666)\
**Post date:** [August 15, 2014, 1:31pm UTC](https://forums.percona.com/t/mysql-get-latest-records-by-order-by-desc-without-subquery/3675/3 "2014-08-15T13:31:43Z")

</div>

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.