SQL nested query and use of MAX to extract most recent transaction and/or comment

We have a SQL database table recording customer comments (ARCMM). I want to extract the most recent comment for each customer. Some customers do not have any comments (i.e. no entries in ARCMM). The most recent comment for a customer will have the most recent date (field DATEENTR) and, for that date, the highest value of field CNTUNIQ. The query below does not work as expected. Best fix?

Query:

SELECT
----- Customer masterfile
[ARCUS].[IDCUST],
[ARCUS].[NAMECUST],
----- Customer comments
[ARCMM].[CNTUNIQ],
[ARCMM].[DATEENTR],
[ARCMM].[TEXT]
FROM
[ARCUS]  
----- Table ARCMM roto ID AR0021  Customer Comments -----
   LEFT JOIN [ARCMM]
   ON
   [ARCMM].[IDCUST] = [ARCUS].[IDCUST]
   AND
   [ARCMM].[CNTUNIQ] =              
                  (  
                   SELECT MAX([CNTUNIQ])
                   FROM [ARCMM] ARCMMcopy2
                   WHERE 
                   [ARCMMcopy2].[IDCUST] = [ARCMM].[IDCUST]   
                   AND
                   [ARCMM].[DATEENTR] =  
                                       (
                                        SELECT MAX([DATEENTR])    
                                        FROM [ARCMM] ARCMMcopy1
                                        WHERE
                                        [ARCMMcopy1].[IDCUST]  = [ARCMM].[IDCUST]
                                        )
                   )

​x
 
SELECT----- Customer masterfile[ARCUS].[IDCUST],[ARCUS].[NAMECUST],----- Customer comments[ARCMM].[CNTUNIQ],[ARCMM].[DATEENTR],[ARCMM].[TEXT]FROM[ARCUS]  ----- Table ARCMM roto ID AR0021  Customer Comments -----   LEFT JOIN [ARCMM]   ON   [ARCMM].[IDCUST] = [ARCUS].[IDCUST]   AND   [ARCMM].[CNTUNIQ] =                                (                     SELECT MAX([CNTUNIQ])                   FROM [ARCMM] ARCMMcopy2                   WHERE                    [ARCMMcopy2].[IDCUST] = [ARCMM].[IDCUST]                      AND                   [ARCMM].[DATEENTR] =                                         (                                        SELECT MAX([DATEENTR])                                            FROM [ARCMM] ARCMMcopy1                                        WHERE                                        [ARCMMcopy1].[IDCUST]  = [ARCMM].[IDCUST]                                        )                   )    ​

Sample table ARCMM data:

IDCUST  DATEEENTR  CNTUNIQ  TEXT
Bob     20200311        1   Bob has woken up
Bob     20200311        2   Bob is having breakfast
Bob     20200629        1   Bob is sleeping         <most recent for IDCUST Bob
Jill    20200128        1   Order started
Jill    20200218        1   Order sent
Jill    20200218        2   Goods received
Jill    20200218        3   Goods counted
Jill    20200325        1   Invoice received
Jill    20200325        2   Invoice processed       <most recent for IDCUST Jill
Alison  20200225        1   Swimming
Alison  20200425        1   Walking
Alison  20200425        2   Running
Alison  20200425        3   Running
Alison  20200425        4   Sprinting
Alison  20200425        5   Jogging
Alison  20200425        6   Stopped                 <most recent for IDCUST Alison

 
IDCUST  DATEEENTR  CNTUNIQ  TEXTBob     20200311        1   Bob has woken upBob     20200311        2   Bob is having breakfastBob     20200629        1   Bob is sleeping         <most recent for IDCUST BobJill    20200128        1   Order startedJill    20200218        1   Order sentJill    20200218        2   Goods receivedJill    20200218        3   Goods countedJill    20200325        1   Invoice receivedJill    20200325        2   Invoice processed       <most recent for IDCUST JillAlison  20200225        1   SwimmingAlison  20200425        1   WalkingAlison  20200425        2   RunningAlison  20200425        3   RunningAlison  20200425        4   SprintingAlison  20200425        5   JoggingAlison  20200425        6   Stopped                 <most recent for IDCUST Alison​

Results from my SQL query attempt:

IDCUST   NAMECUST          CNTUNIQ   DATEENTR   TEXT
Bob      Bob Brown         Null      Null       Null
Jill     Jill Jenkins      Null      Null       Null
Alison   Alison Allpress   6         20200425   Stopped

 
IDCUST   NAMECUST          CNTUNIQ   DATEENTR   TEXTBob      Bob Brown         Null      Null       NullJill     Jill Jenkins      Null      Null       NullAlison   Alison Allpress   6         20200425   Stopped ​

Desired results:

IDCUST   NAMECUST          CNTUNIQ   DATEENTR   TEXT
Bob      Bob Brown         1         20200629   Bob is sleeping
Jill     Jill Jenkins      2         20200325   Invoice processed
Alison   Alison Allpress   6         20200425   Stopped

 
IDCUST   NAMECUST          CNTUNIQ   DATEENTR   TEXTBob      Bob Brown         1         20200629   Bob is sleepingJill     Jill Jenkins      2         20200325   Invoice processedAlison   Alison Allpress   6         20200425   Stopped ​

Answer

You could use row_number() within the left join, if your database supports window functions:

SELECT
    c.[IDCUST],
    c.[NAMECUST],
    m.[CNTUNIQ],
    m.[DATEENTR],
    m.[TEXT]
FROM [ARCUS] c 
LEFT JOIN (
    SELECT 
        m.*, 
        ROW_NUMBER() OVER(
            PARTITION BY [IDCUST] 
            ORDER BY [DATEENTR] DESC, [CNTUNIQ] DESC
        ) rn
    FROM [ARCMM] m
) m ON m.[IDCUST] = c.[IDCUST] and m.rn = 1

 
SELECT    c.[IDCUST],    c.[NAMECUST],    m.[CNTUNIQ],    m.[DATEENTR],    m.[TEXT]FROM [ARCUS] c LEFT JOIN (    SELECT         m.*,         ROW_NUMBER() OVER(            PARTITION BY [IDCUST]             ORDER BY [DATEENTR] DESC, [CNTUNIQ] DESC        ) rn    FROM [ARCMM] m) m ON m.[IDCUST] = c.[IDCUST] and m.rn = 1​

Advertisement

Answer