SAS EG(SQL)删除一列中有最大值的行 [英] SAS EG (SQL) deleting rows where max value in one column

问题描述

我需要删除最大值为 duty_perd_id 的所有行，其中 rotn_prng_nbr 和 empl_nbr 相同(与彼此，但是这两个保持不变的所有行的最大位置).从下表中它应该删除第 3,7 和 9 行.

I need to delete all the rows with a max value of duty_perd_id where the rotn_prng_nbr and empl_nbr are the same (not the same to each other, but the max where of all of the rows where those two remain constant). From the table below it should delete rows 3,7 and 9.

<头>

rotn_prng_nbr	empl_nbr	duty_perd_id
B93	12	1
B93	12	2
B93	12	3
B21	12	1
B21	12	2
B21	12	3
B21	12	4
B21	18	1
B21	18	2

使用 SAS EG.现在所有的都在下面:

using SAS EG. Right now all have is below:

选项 1:

 create table middle_legs as
    select t.*
    from actual_flt_leg as t
    where t.duty_perd_id < (select max(t2.duty_perd_id)
                            from actual_flt_leg as t2
                            where t2.rotn_prng_nbr = t.rotn_prng_nbr and
                                  t2.empl_nbr = t.empl_nbr
                           );

这完全符合预期，但速度非常慢.另一个我有但无法完成的想法如下.

this works exactly as intended, but is incredibly slow. The other thought that I had but couldnt quite finish was as follows.

选项 2:

create table last_duty_day as
Select * from actual_flt_leg 
inner join (
    select actual_flt_leg.Rotn_Prng_Nbr,actual_flt_leg.empl_nbr, max(duty_perd_id) as last_duty
    from actual_flt_leg
    group by actual_flt_leg.Rotn_Prng_Nbr, actual_flt_leg.empl_nbr
) maxtable on 
    actual_flt_leg.Rotn_Prng_Nbr = maxtable.Rotn_Prng_Nbr
    and actual_flt_leg.empl_Nbr = maxtable.empl_Nbr
    and actual_flt_leg.duty_perd_id = maxtable.last_duty;

选项 2 找到给定对的所有最高 duty_perd_id，我想知道是否有任何反向连接"；只能显示原始表中与我在选项 2 中创建的新表不匹配的行.

option 2 finds all the highest duty_perd_id for the given pair, and I was wondering if there was any "reverse join" that could only show the rows from the original table that do not match this new table i created in option 2.

如果有办法让选项 1 更快，请完成选项 2，或者我想不出的任何其他事情，我很感激.谢谢！

If there is a way to make option 1 faster, finish option 2, or anything else i cant think of id appreciate it. Thanks!

推荐答案

你快到了.你只想要 <:

You are almost there. You just want <:

Select *
from actual_flt_leg inner join
     (select actual_flt_leg.Rotn_Prng_Nbr,actual_flt_leg.empl_nbr, max(duty_perd_id) as last_duty
      from actual_flt_leg
      group by actual_flt_leg.Rotn_Prng_Nbr, actual_flt_leg.empl_nbr
     ) maxtable 
     on actual_flt_leg.Rotn_Prng_Nbr = maxtable.Rotn_Prng_Nbr and
        actual_flt_leg.empl_Nbr = maxtable.empl_Nbr and
        actual_flt_leg.duty_perd_id < maxtable.last_duty;

这篇关于SAS EG(SQL)删除一列中有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！