SQL MAX aggregate function not bringing the latest date

Purpose: I am trying to find the max date of when the teachers made a purchase and type.

Orders table

ID	Ordertype	Status	TeacherID	PurchaseDate	SchoolID	TeacherassistantID
1	Pencils	Completed	1	1/1/2021	1	1
2	Paper	Completed	1	3/5/2021	1	1
3	Notebooks	Completed	1	4/1/2021	1	1
4	Erasers	Completed	2	2/1/2021	2	2

Teachers table

TeacherID	Teachername
1	Mary Smith
2	Jason Crane

School table

ID	schoolname
1	ABC school
2	PS1
3	PS2

Here is my attempted code:

SELECT o.ordertype, o.status, t.Teachername, s.schoolname
,MAX(o.Purchasedate) OVER (PARTITION by t.ID) last_purchase
FROM orders o
INNER JOIN teachers t ON t.ID=o.TeacherID
INNER JOIN schools s ON s.ID=o.schoolID
WHERE o.status in ('Completed','In-progress')
AND o.ordertype not like 'notebook'

It should look like this:

Ordertype	Status	teachername	last_purchase	schoolname
Paper	Completed	Mary Smith	3/5/2021	ABC School
Erasers	Completed	PS1	2/1/2021	ABC school

It is bringing multiple rows instead of just the latest purchase date and its associated rows. I think i need a subquery.

Solution

Aggregation functions are not appropriate for what you are trying to do. Their purpose is to summarize values in multiple rows, not to choose a particular row.

Just a window function does not filter any rows.

You want to use window functions with filtering:

SELECT ordertype, status, Teachername, schoolname, Purchasedate
FROM (SELECT o.ordertype, o.status, t.Teachername, s.schoolname,
             o.Purchasedate,
           ROW_NUMBER() OVER (PARTITION by t.ID ORDER BY o.PurchaseDate DESC) as seqnum
      FROM orders o JOIN
           teachers t
           ON t.ID = o.TeacherID
           schools s
           ON s.ID = o.schoolID
      WHERE o.status in ('Completed', 'In-progress') AND
            o.ordertype not like 'notebook'
     ) o
WHERE seqnum = 1;