SQL query using MAX with multiple table joins

Question

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.

score 0 · Answer 1 · answered Mar 26 '23 at 10:28

Try this

SELECT 
  MWPAT."Chart Number", 
  MWPAT."First Name", 
  (SELECT MAX(MWCAS2."Case Number")
   FROM MWCAS MWCAS2
   WHERE MWCAS2."Chart Number" = MWPAT."Chart Number") AS "Case Number", 
  MWCAS."Marital Status"
FROM MWPAT MWPAT  
LEFT OUTER JOIN MWCAS MWCAS ON 
      (MWCAS."Chart Number" = MWPAT."Chart Number")
LEFT OUTER JOIN MWINS MWINS ON 
     (MWINS.Code = MWCAS."Insurance Carrier #1")
LEFT OUTER JOIN MWTRN MWTRN ON 
     (MWTRN."Chart Number" = MWPAT."Chart Number")
Where MWTRN."Date From"> '01/01/2000'
ORDER BY  MWPAT."Chart Number"

Without a DBFiddle to verify, I may have made a typo.

Note

The left outer join gets only the rows from the second table that match the first
The sub-select gets the maximum value for the Case Number.
Since MWCAS is used twice, I have used a different alias for it.

SQL query using MAX with multiple table joins

1 Answers1