Not the answer you need?
Register and ask your own question!

mysql get latest records by order by desc without subquery

infdude666infdude666 ContributorCurrent User Role Beginner
[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 )    

Comments

  • psongpsong Contributor Inactive User Role Beginner
    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;
    
  • infdude666infdude666 Contributor Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.