在mysql中获取具有最大列值的行

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.

从上面的结果集中,我需要提取给定transition_ident和star_ident具有最大sequence_num的那些行。这是我的查询。

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.

但我最终得到零行。为我提供了一个有效的方法。我需要为13K条目运行此查询。谢谢。

3 个解决方案

#1

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

您的选择中的非聚合列不属于分组依据。

FROM MYSQL DOCS

来自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....

希望能帮助到你....

#2

Try this:

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

Here is the sqlfiddle

这是sqlfiddle

#3

remove group by start_ident

通过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