title: Getting row with maximum column value in mysql
body: <p>I have been struggling with this.I had tried all the approaches like left outer join,group by and even sub queries but couldn't succeed.
  Here is my query.</p>
  
  <pre><code>select star_ident,transition_ident,fix_ident,sequence_num,route_type
  from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU';  
  </code></pre>
  
  <p>From the above result set i need to extract those rows that has maximum sequence_num for a given transition_ident and star_ident. 
  Here is my query.  </p>
  
  <pre><code>select star_ident,transition_ident,fix_ident,max(sequence_num)
  from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU'
  group by transition_ident,star_ident;
  </code></pre>
  
  <p>But the above query is producing wrong results.I even tried joins.</p>
  
  <pre><code>select yt1.star_ident,yt1.transition_ident,yt1.fix_ident
  from corept.std_star_leg yt1
  left outer join corept.std_star_leg yt2
  on (yt1.star_ident = yt2.star_ident and yt1.transition_ident=yt2.transition_ident and      yt1.sequence_num &lt; yt2.sequence_num)
  where yt1.airport_ident='KMMU' and yt1.data_supplier='J'
  and yt2.airport_ident='KMMU' and yt2.data_supplier='J' and yt2.star_ident is null;
  </code></pre>
  
  <p>But i end up with zero rows.Provide me an efficient way to do this.I need to run this query for 13K entries.Thank you.</p>