Get min from one column and each value from another only once with min rank-CodePudding

Problem: We want only the minimum jobrank for each df. Then among those, we want every job to appear, but only once with the minimum mf value. Here's the table structure I have I have one table with DF entries. Lets call it DfTable

DF
1
2
3

Another table2 as follows

MF  DF   Job  JobRank
-------------------
1   1    1     1
2   1    1     1
3   1    2     1
4   2    3     2
5   3    4     3
6   3    5     4

MF is unique, DF can be multiple.

Output should be: MF should be unique, DF should be unique per Job with minimum rank.

MF  DF   Job
-------------
1   1    1
3   1    2
4   2    3
5   3    4

So far this is what I have:

Select T2.MF, T1.DF, T2.Job
From table1 As T1
Inner Join table2 As T2 On T1.DF = T2.DF
Where JobRank = (
    Select Min(JobRank)
    From table2 As T3
    Where T3.DF = T2.DF
)

CodePudding user response：

I think I understand now. You want only the minimum jobrank for each df. Then among those, you want every job to appear, but only once with the minimum mf value.

Here is a method using window functions:

select t.*
from (select t.*,
             row_number() over (partition by df, job, jobrank order by mf) as seqnum_djj,
             min(jobrank) over (partition by df) as min_jobrank
      from t
     ) t
where seqnum_djj = 1 and jobrank = min_jobrank;

Here is a db<>fiddle.