Dear ladies and gentlemen, Unfortunately I'm totally new to sql programming, so I apologize for any definite errors or lack of knowledge - For my Access database i have a table "report" with "reportnumber" as id (there can be multiple versions of each report with different dates) as well as the "reportdate".
I now want my query to display ONLY the last "reportnumber"s with the latest "reportdate"s.
I've tried the following sql code I have found and adapted it but it only gives me error messages (syntax in declaration WITH OWNER ACCESS OPTION?):
SELECT reportnumber, MAX(reportdate) AS LatestDate FROM report
GROUP BY reportnumber
WITH numbered AS ( SELECT reportnumber, reportdate
ROW_NUMBER() OVER (PARTITION BY reportnumber
ORDER BY reportdate DESC) AS rownum FROM report )
SELECT reportnumber, reportdate
FROM numbered WHERE rownum = 1;
I'd really appreciate your help. Thank you very much and kind regards,
Daniel Funk