SQL query using MAX with multiple table joins

I have a working SQL script to show results that join multiple tables. However, I want to limit the results of the join to show only one record per a given "Chart Number" value in one of the tables. Here is the original query: SELECT MWPAT."Chart Number", MWPAT."First Name", MWCAS."Case Number", MWCAS."Marital Status" FROM MWPAT MWPAT INNER JOIN MWCAS MWCAS ON (MWCAS."Chart Number" = MWPAT."Chart Number") INNER JOIN MWINS MWINS ON (MWINS.Code = MWCAS."Insurance Carrier #1") INNER JOIN MWTRN MWTRN ON (MWTRN."Chart Number" = MWPAT."Chart Number") Where MWTRN."Date From"> '01/01/2000' GROUP BY MWPAT."Chart Number", MWPAT."First Name", MWCAS."Case Number", MWCAS."Marital Status" ORDER BY MWPAT."Chart Number" And here is the sample output for this query: | Chart Number | First Name | Case Number | Marital Status | | :----------- | :--------- | :---------- | :------------- | | 000001 | John | 2 | Single | | 000001 | John | 8 | Single | | 000001 | John | 15 | | | 000005 | Sarah | 35 | Single | | 000005 | Sarah | 42 | Married | | 000009 | Fred | 7 | Single | | 000036 | Mary | 89 | Divorced | I need to limit this output to only show one line per "Chart Number" column of the MWCAS table, based on the highest "Case Number" value that appears for the given Chart Number. For example- the output would show this instead: | Chart Number | First Name | Case Number | Marital Status | | :----------- | :--------- | :---------- | :------------- | | 000001 | John | 15 | | | 000005 | Sarah | 42 | Married | | 000009 | Fred | 7 | Single | | 000036 | Mary | 89 | Divorced | Note that each Chart Number only shows one line now, which is based on the information of the MWCAS table, and choosing the highest Case Number value for the output. I have tried different uses of MAX but I cannot find a syntax that works. I'm querying Advantage Database, so this could be limiting. Appreciate any suggestions.