-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
  • 52,284
  • 6
  • 19
  • 43
VSB
  • 9,825
  • 16
  • 72
  • 145
  • 3
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Feb 06 '22 at 10:37
  • 2
    for someone with >8k reputation you should surely know that images of data are frowned upon. – Stu Feb 06 '22 at 10:47
  • Take the duplicate, apply a `WHERE`. – Thom A Feb 06 '22 at 11:17

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
  • 2,089
  • 1
  • 19
  • 36