I have table "Serials" with data as follow:

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(FinalStatus)
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.

CodePudding user response：

One way you can try to use NOT EXISTS with a subquery to instead HAVING

SELECT [Serial], MAX([Date]),  MAX(FinalStatus)
FROM [Serials] t1
WHERE NOT EXISTS (
    SELECT 1
    FROM [Serials] tt
    WHERE 
        t1.Serial = tt.Serial
    AND 
        tt.Status = 'GOOD'
)
GROUP BY [Serial]

sqlfiddle

if you met performance problem on your query, you might look at your indexes in your table.

You might create an index that put Serial in the first column in your table, otherwise you need to create one to fix like below.

CREATE INDEX IX_Serials_idx ON Serials (Serial)

CodePudding user response：

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'