CODEWITHSUNDEEP
sqlsql-serverwhere-clausehaving
Amikot40
Amikot40

Reputation: 38

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.

Upvotes: 0

Views: 737

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146551

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.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86765

One option is to use ROW_NUMBER() to find the last row for each Serial, and then filter out those rows with a Good Status.

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY Serial ORDER BY [date] DESC)   AS serial_row   
  FROM
    serials
)
SELECT
  *
FROM
  sorted
WHERE
      serial_row  = 1
  AND status     != 'GOOD'

Upvotes: 1

Related Questions