SQL question - select max of a group

[Aharami]

SQL newb here looking for some help

I have the following query

SELECT MPH_SITE_ID,                                
       MPH_BASE_CONTRACT_NBR,                      
       MPH_PROC_CD,                                
       MPH_MOD_PATTERN_CD,                         
       MPH_MOD_PRICE_AMT,                          
       MPH_RSN_CD,                                 
       MPH_EFF_CYMD,                               
       MPH_EXP_CYMD,                               
       MPH_SEQ_NBR,                                
       MPH_FM_CYMD,                                
       MPH_FM_USER_CD                              
FROM MPH_DATA                             
WHERE MPH_SITE_ID = 'ETX'                          
  AND (MPH_BASE_CONTRACT_NBR BETWEEN               
       00003 AND 00003)                                      
ORDER BY MPH_PROC_CD DESC, 
         MPH_MOD_PATTERN_CD DESC,
         MPH_EFF_CYMD DESC, 
         MPH_SEQ_NBR DESC

which returns the following (colums in red are indexes)

What I need is to select the orange highlighted rows - which is:

for each combination of MPH_PROC_CD, MPH_MOD_PATTERN_CD, and MPH_EFF_CYMD, select the row with the highest MPH_SEQ_NBR

I am not sure how to do this. Any tips?

 

[Ka0t1x]

You could just simply order by the MPH_SEQ_NBR desc and limit to one result.

This could be done with a subquery if you need it in a current result-set.

 

[MrChad]

select
    mph_proc_cd,
    mph_mod_pattern_cd,
    mph_eff_cymd,
    max(mph_seq_nbr)
from
    mph_data                             
where
    mph_site_id = 'etx' and
    (mph_base_contract_nbr between 00003 and 00003)
group by
    mph_proc_cd,
    mph_mod_pattern_cd,
    mph_eff_cymd

 

[Aharami]

MrChad, your query works if I only needed to pull in those four cols, but I need all the cols I listed initially

I have this, but not sure if it is the most efficient

SELECT M1.MPH_SITE_ID,                                
       M1.MPH_BASE_CONTRACT_NBR,                      
       M1.MPH_PROC_CD,                                
       M1.MPH_MOD_PATTERN_CD,                         
       M1.MPH_MOD_PRICE_AMT,                          
       M1.MPH_RSN_CD,                                 
       M1.MPH_EFF_CYMD,                               
       M1.MPH_EXP_CYMD,                               
       M1.MPH_SEQ_NBR,                                
       M1.MPH_FM_CYMD,                                
       M1.MPH_FM_USER_CD                              
FROM D9331CSY.MPH_DATA M1                             
WHERE M1.MPH_SITE_ID = 'ETX'                          
  AND (M1.MPH_BASE_CONTRACT_NBR BETWEEN               
       00003 AND 00003)
  AND M1.MPH_SEQ_NBR = 
                (Select max(M2.MPH_SEQ_NBR) 
                 FROM D9331CSY.MPH_DATA M2                             
                 WHERE M2.MPH_SITE_ID = 'ETX'                          
                    AND M2.MPH_BASE_CONTRACT_NBR BETWEEN 00003 and 00003 
                    AND M2.MPH_PROC_CD = M1.MPH_PROC_CD 
                    AND M2.MPH_MOD_PATTERN_CD = M1.MPH_MOD_PATTERN_CD
                                AND M2.MPH_EFF_CYMD = M1.MPH_EFF_CYMD)
ORDER BY MPH_PROC_CD DESC, MPH_MOD_PATTERN_CD DESC,
MPH_EFF_CYMD DESC, MPH_SEQ_NBR DESC

 

[MrChad]

What's the primary key of the table?

 

[Bobalude]

I'm still learning SQL techniques but could you do something like... adding this to your select:

RANK() OVER(PARTITION BY MPH_PROC_CD, MPH_MOD_PATTERN_CD, MPH_EFF_CYMD ORDER BY MPH_SEQ_NBR DESC) AS temp_rank

to rank each MPH_SEQ_NBR by your 3 criteria groups, where the highest MPH_SEQ_NBR is assigned "1".

wrap that entire query with:

SELECT *
FROM
( [query with rank] )
WHERE temp_rank = 1