Getting row with maximum column value in mysql

Question

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.

select star_ident,transition_ident,fix_ident,sequence_num,route_type
from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU';

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.

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;

But the above query is producing wrong results.I even tried joins.

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

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.

good question, one would have thought there is a way to get the value of a field where the max() of another value is found. The current accepted answer may work but it would have been nice if there is a more efficient query as you request. — akostadinov
– akostadinov, Commented Apr 12, 2013 at 10:31

Meherzad · Accepted Answer · 2013-04-12 08:23:24Z

You are having nonaggregated columns in your select which are not part of group by.

FROM MYSQL DOCS

MySQL extends the use of `GROUP BY` so that the select list can refer to nonaggregated
columns not named in the GROUP BY clause. You can use this feature to get better
performance by avoiding unnecessary column sorting and grouping. However, this is 
useful primarily when all values in each nonaggregated column not named in the 
GROUP BY are the same for each group. The server is free to choose any value from 
each group, so unless they are the same, the values chosen are indeterminate.

So to get proper result add all the column of select in group by

EDIT

select b.* 
from 
corept.std_star_leg b
inner join
(select star_ident,transition_ident,max(sequence_num) as seq
from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU'
group by star_ident,transition_ident) a
on b.star_ident = a.star_ident and a.transition_ident = b.transition_ident and
b.sequence_num = a.seq;

Hope it helps....

Alepac · Accepted Answer · 2013-04-12 08:20:34Z

1

Try this:

SELECT *
FROM
  (SELECT *
   FROM table1
   ORDER BY seqno DESC) tmp
GROUP BY `star_ident`,
         `trans`

Here is the sqlfiddle

answered Apr 12, 2013 at 8:20

Alepac

1,84313 silver badges24 bronze badges

Comments

alwaysLearn · Accepted Answer · 2013-04-12 07:01:45Z

0

remove group by start_ident

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

answered Apr 12, 2013 at 7:01

alwaysLearn

6,9507 gold badges45 silver badges69 bronze badges

3 Comments

alwaysLearn Over a year ago

Please try this out and revert

user218045 Over a year ago

I even tried that tooo...but it is producing wrong o/p 'JAIKE3', 'ALL', 'JAIKE', 70 'JAIKE3', 'FAK', 'FAK', 90 'JAIKE3', 'GVE', 'GVE', 90 'JAIKE3', 'HPW', 'HPW', 80 'LVZ4', 'JHW', 'JHW', 50 'JAIKE3', 'PXT', 'PXT', 90 'JAIKE3', 'RIC', 'RIC', 80 'MAZIE2', 'SWANN', 'SWANN',80

user218045 Over a year ago

It is replacing fix_ident as same that of transition_ident...in the above comment if u consider the row JAIKE3 FAK FAK 90...the fix_ident JAIKE is replaced with FAK which is transition_ident

Collectives™ on Stack Overflow

Getting row with maximum column value in mysql

3 Answers 3

EDIT

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

EDIT

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related