group by order but found SELECT syntax

Question

A select SQL with a subquery that contains a descending sort and inner join to link unto saem id (patid). Once this sort subquery is done I will make it a view query to be used by another SQL which is not working. Using WINSQL with Cache database.

The idea is to sort data_of_assessment in Descending order

SELECT c1.patid, c1.date_of_assessment, c1.form_status_value
FROM cw_cans_assessment c1 
INNER JOIN (
    SELECT * from cw_cans_assessment c2
    ORDER BY c2.data_of_assessement DESC) 
    ON c1.patid = c2.patid
GROUP BY c1.patid

Error message:

[%msg: < ) expected, IDENTIFIER (order) found^SELECT c1 . patid , c1 . date_

Sample Data:

patid   date_of_assessment  form_status_value
64050   2009-06-25          Unknown
53297   2011-12-07          Unknown
42308   2016-06-16          Initial
9540757 2016-09-22          Initial
45144   2017-06-23          Reassessment
50529   2017-09-01          Initial
38557   2017-10-17          Initial
52754   2017-10-20          Initial
50123   2017-11-07          Initial
15572   2017-11-14          Initial

Answer 1

Presumably, what you want is the most recent assessment for each patid:

select ca.*
from cw_cans_assessment ca
where ca.date_of_assessment = (select max(ca2.date_of_assessment)
                               from cw_cans_assessment ca2
                               where ca2.patid = ca.patid
                              );