Greetings I need to SQL Query the CN00100 Table (Collection Module - Notes) and find the latest date that a customer note has been created.
This query works (kind of)
select CN.CUSTNMBR, CN.DATE1
from CN00100 CN
where CN.DATE1 = (select max(CN2.DATE1)
from CN00100 CN2
where CN.CUSTNMBR = CN2.CUSTNMBR
)
the issue is if the customer has multiple notes on the same day - query returns multiple rows (I only want one row).
How would I incorporate a TOP 1 or do I use DEX_ROW_ID instead of DATE1? Your thoughts?
Here's a sample of the dataset. YGLPR93752 has 3 notes on May 15th - I want a single row returned instead of three rows.
