cancel
Showing results for 
Search instead for 
Did you mean: 

MAX display only the newest record

kangdinh
Discoverer
0 Kudos
250

I have two tables, one just a customer information and the other is all the casenotes tie to that customer.  I just want to see the latest casenote ONLY for my customer.  I create this query but it giving all the casenotes.  Please help.

SELECT
c.userID,
c.FirstName,
c.LastName,
MAX(cn.noteDate) as "Last CN Date"
FROM UsercustomerDetails c
LEFT JOIN Casenotes cn
ON c.UserID=cn.UserID
Where c.userid='309277851'
GROUP BY c.UserID, c.FirstName, c.LastName,cn.noteDate,cn.title,cn.note

Result:

kangdinh_0-1735602020809.png

I need to make it display the last record only.

 

View Entire Topic
Andrei_Karanchuck
Product and Topic Expert
Product and Topic Expert

Hi @kangdinh,

I have reviewed your scenario and created two tables to replicate it. The first table customer contains customer information, and the second table note contains case notes associated with each customer. I populated these tables with sample data. Below in the image, I’ve highlighted the specific userId and their latest case note.

Andrei_Karanchuck_0-1735902771061.png

Query:

SELECT c~userID,
           c~FirstName,
           c~LastName,
           cn~noteDate AS LastNoteDate,
           cn~title,
           cn~note
      FROM customer AS c 
             LEFT JOIN
               note AS cn ON c~userID = cn~userID 
      WHERE c~userID    = '309277851'
        AND cn~noteDate = ( SELECT MAX( noteDate ) FROM note
                              WHERE userID = c~userID )
      INTO TABLE @DATA(lt_result).

For validation, I created a class that outputs the result in the console. Below, you'll see the result in the console as a screenshot.

Andrei_Karanchuck_1-1735902854544.png

This approach ensures that only the most recent note for the customer is retrieved correctly.

BR,
Andrei

 

kangdinh
Discoverer
0 Kudos
This is working. Only one thing is I also would like to see the customers that have no case notes put in. Still this is great and thank you.
kangdinh
Discoverer
0 Kudos
Is there a way in that SELECT(MAX codes allow the query to pick up CUSTOMER that has no casenote.