Distinct without group by/having/distinct

I am trying to find an appropriate solution to the following problem: I have an activity-tracking database and I want to get the status at a specific date (ex: 11-Sept)

Activity	Status	Date
100	Done	12-Sept
100	In prog	10-Sept
110	In prog	12-Sept
110	In prog	09-Sept
110	New	08-Sept

My current query is: select * from table where Date <= 11-Sept My current output is:

Activity	Status	Date
100	In prog	10-Sept
110	In prog	09-Sept
110	New	08-Sept

Problem is I want to limit to only 1 row (newest date) for each activity code. Desired output is:

Activity	Status	Date
100	In prog	10-Sept
110	In prog	09-Sept

BUT...

I can't user group by, having or distinct, so the following solutions are not acceptable:

select distinct Name
from table
where Date <= 11-Sept

select name, max(date)
from table 
where Date <= 11-Sept
group by name

Basically.. I can only use the where clause

Anyone has an idea?

Desired output is:

Activity	Status	Date
100	In prog	10-Sept
110	In prog	09-Sept

To be mentioned: the format of the date here is simplified

Solution

One canonical way to do this uses the ROW_NUMBER() window function:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Activity ORDER BY Date DESC) rn
    FROM yourTable
    WHERE Date < '20230911'
)

SELECT Activity, Status, Date
FROM cte
WHERE rn = 1
ORDER BY Activity;

Another more concise (yet less performant) way:

SELECT TOP 1 WITH TIES Activity, Status, Date
FROM yourTable
WHERE Date <= '20230911'
ORDER BY ROW_NUMBER() OVER (PARTITION BY Activity ORDER BY Date DESC);

A third option, using exists logic:

SELECT Activity, Status, Date
FROM yourTable t1
WHERE
    Date <= '20230911' AND
    NOT EXISTS (
        SELECT 1
        FROM yourTable t2
        WHERE t2.Date <= '20230911' AND
              t2.Activity = t1.Activity AND
              t2.Date > t1.Date
    )
ORDER BY Activity;