How to select only not related rows without using HAVING clause

I have table "Serials" with data as follow:

Serial	Date	Status
A00010	03.03.2022	BAD
A00010	04.03.2022	GOOD
A00011	05.03.2022	BAD
A00012	06.03.2022	BAD

I would like to select only those serial number that their actual status is not GOOD. So as result I expect only A00011 and A00012. This is because A00010 latest (look on Date) status is GOOD. Query should return only DISTINCT values (Here I used grouping by Serial).

Only solution I've found is with HAVING clause:

SELECT [Serial], MAX([Date]),  MAX([Status])
FROM [Serials]
GROUP BY [Serial]
HAVING MAX([Status]) != 'GOOD'

I would prefer to avoid using it as it uses more resources (specially when bigger tables are scanned). Additionally, most of Serials in table will have latest record as GOOD anyway, so current Query will pull almost whole table and then HAVING will just crop most of it. For big table it would be big waste of resources. So if someone could help me to find the query that will do the same but with WHERE clause only. Thanks.

Solution

Try this:

Select Serial
From Serials s
Where s.Date = 
   (Select Max(Date) From Serials
        Where Serial = s.Serial) 
   And Status != 'Good'

If there can exist more than one record in the table with same Serial and Date, then add the word Distinct

Select Distinct Serial
From Serials s
Where s.Date = 
    (Select Max(Date) From Serials
        Where Serial = s.Serial) 
   And Status != 'Good'

To also address your request in third comment, (to only select Serials that have previously been Good), just add that as an additional predicate to Where clause:

Select Distinct Serial
From Serials s
Where s.Date = 
    (Select Max(Date) From Serials
        Where Serial = s.Serial) 
   And Status != 'Good'
   And exists                  -- This predicate ensures that only 
      (Select * from Serial x   -- previously 'GOOD' serials 
       Where serial = s.Serial  -- will appear
          and Date < (Select Max(Date) From Serials
                      Where Serial = s.Serial) 
          and status = 'GOOD')

Basically, what you want to do can be translated directly into SQL:

You want a [distinct] list of those Serials that are a set of the last chronological record of each specific serial, filtered to only include the Not Good ones. So you create a sub table that only includes the records where the date is the latest date for that serial, then filter it by status to only include the not good ones, then extract the serial value from that table.

Depending on how large the table is, This will work very well (or better) if you have an index on the Date attribute.