Help with combining two queries into one I have two tables, s and c.

This query works for the inner join

select s.cname, c.HName 
from c inner join s on (c.pid = s.pid AND c.cid = s.cid) 


I'm having trouble with a where clause. There's a flag in table c indicating an error, and I want to display only the names where the error flag is set to 'Y', but it also has to be the most recent entry (checks every hour so many many entries).

So far I have this:

select max(date),cid,pid, case WHEN error_flag='Y' THEN 'Y' ELSE 'N' END AS "Still has Error"
from c
group by cid,pid,error_flag


That query does the job, but I need to essentially make a where clause for the first query that achieves the same thing. Any suggestions?

Note: Using SQL server 2016
Assuming your first query has error_flag and date in one of the tables then something like this would probably work:

SELECT ROW_NUMBER() OVER ( ORDER BY s.Date DESC ) AS Rownum ,
       s.cname ,
       c.HName
FROM   c
       INNER JOIN s ON (   c.pid = s.pid
                           AND c.cid = s.cid )
WHERE s.error_Flag = 'Y' AND Rownum = 1;