-2

I have an SQL database with below rules:

  • There are items with same name but different versions.
  • Status of item production is stored inside a column State.

The structure of table log inside test database is like below:

id Name Ver State
1 A 1 OK
2 B 1 Failed
3 C 1 OK
4 D 1 OK
5 A 2 OK
6 B 2 OK
7 C 2 Failed
8 D 2 OK
9 A 3 Failed

According to our production rules, the last version made from an item should have state OK (not Failed). So I want a query to grab list of unacceptable items with below constraints:

  • Name of items which their last version state is 'Failed'

In my sample data, the query should return A and C since Max version of A is 3 but its state is 'Failed' and max version of C is 2 but its state is Failed.

B and D are OK since their last version is 2 and the state of the version 2 for these items is OK.

How can I write query to bring list of items which their last version is failed (and not OK)?

Charlieface
  • 29,562
  • 5
  • 12
  • 30
VSB
  • 8,918
  • 12
  • 64
  • 128

1 Answers1

1

Ah, yes, super rusty. Yet, you can definitely do this with a subquery:

SELECT mytable.name, mytable.ver, mytable.state 
from
(SELECT NAME, VER
FROM TABLE GROUP BY NAME
HAVING VER = max(VER)) subq
INNER JOIN table on subq.name = mytable.name and subq.ver = mytable.ver
WHERE mytable.STATE = 'failed'

This is not a performant answer.

Dylan Brams
  • 1,989
  • 1
  • 17
  • 33