Suppress inappropriate duplicates in a report

Question

The medical records in my Crystal Report are sorted in the following order:

...
Group 1: Score [Level of Risk]
  Group 2: Patient Name
...

Because patients are sorted according Scoreto Name, the report draws several entries on one patient at different points - and as the duplicate records are not always related, I can not use Previous, or Nextto suppress them. To fix this, I would like to display only the last record for each patient based on the field Assessment Date- while maintaining the above order.

I am convinced that this behavior can be implemented using a special SQL command to pull the last record per patient, but was not successful in creating this behavior. How can I do this complicated sort?

---

Current SQL statement:

SELECT "EpisodeSummary"."PatientID",
"EpisodeSummary"."Patient_Name",
"EpisodeSummary"."Program_Value"
"RiskRating"."Rating_Period",
"RiskRating"."Assessment_Date",
"RiskRating"."Episode_Number",
"RiskRating"."PatientID",
"Facility"."Provider_Name",

FROM (
  "SYSTEM"."EpisodeSummary"
  "EpisodeSummary"
  LEFT OUTER JOIN "FOOBARSYSTEM"."RiskAssessment" "RiskRating"
  ON (
    ("EpisodeSummary"."Episode_Number"="RiskRating"."Episode_Number")
    AND
    ("EpisodeSummary"."FacilityID"="RiskRating"."FacilityID")
  )
  AND
  ("EpisodeSummary"."PatientID"="RiskRating"."PatientID")
), "SYSTEM"."Facility" "Facility"

WHERE (
  "EpisodeSummary"."FacilityID"="Facility"."FacilityID"
)
AND "RiskRating"."PatientID" IS NOT NULL 

ORDER BY "EpisodeSummary"."Program_Value"

Answer 1

SQL , . , " " RiskAssessment. , , .

, row_number . (1). , , № 1 ( №1).

, . Facility? EpisodeSummary ? !

SELECT es.PatientID
    ,es.Patient_Name
    ,es.Program_Value
    ,rrd.Rating_Period
    ,rrd.Assessment_Date
    ,rrd.Episode_Number
    ,rrd.PatientID
    ,f.Provider_Name
FROM SYSTEM.EpisodeSummary es
LEFT JOIN (
    --Derived Table retreiving highest risk score for each patient)
    SELECT PatientID
        ,Assessment_Date
        ,Episode_Number
        ,FacilityID
        ,Rating_Period
        ,ROW_NUMBER() OVER (
            PARTITION BY PatientID ORDER BY Assessment_Date DESC
            ) AS RN -- This code generates a row number for each record. The count is restarted for every patientID and the count starts at the most recent date.
    FROM RiskAssessment
    ) rrd
    ON es.patientID = rrd.patientid
        AND es.episode_number = rrd.episode_number
        AND es.facilityid = rrd.facilityid
        AND rrd.RN = 1 --This only retrieves one record per patient (the most recent date) from the riskassessment table 
INNER JOIN SYSTEM.Facility f
    ON es.facilityid = f.facilityid
WHERE rrd.PatientID IS NOT NULL
ORDER BY es.Program_Value