I have a table like this,
+----------+----------+-----------+------------+
| id | group | info | status |
+----------+----------+-----------+------------+
| 1 | 1 | VALUE123 | EXECUTED |
| 2 | 1 | VALUE123 | INPROGRESS |
| 3 | 1 | VALUE123 | UPLOADED |
| 4 | 1 | VALUE123 | UPLOADED |
| 5 | 1 | VALUE123 | UPLOADED |
| 6 | 1 | VALUE123 | UPLOADED |
| 7 | 2 | VALUE123 | EXECUTED |
| 8 | 2 | VALUE123 | UPLOADED |
| 9 | 2 | VALUE123 | UPLOADED |
| 11 | 2 | VALUE123 | UPLOADED |
| 12 | 2 | VALUE123 | UPLOADED |
| 13 | 2 | VALUE123 | UPLOADED |
+----------+----------+-----------+------------+
I am trying to for a query that should give me a record such that the no row in the table should with the same combination of "group" and "info" would not have status as in "INPROGRESS".
But since I have limited knowledge in forming such complex queries I am not able to make much progress.
For example, the query will be giving me a record from any one of the rows from id [7 - 13] but not from row [1 - 6].
Reason: Since id 2 has its status as 'INPROGRESS', rows [1 - 6] will not satisfy the criteria.
Hope my query makes sense.
You can use not exists:
select t.*
from mytable t
where not exists (
select 1
from mytable t1
wher t1.grp = t.grp and and t1.info = t.info and t1.status = 'INPROGRESS'
)
Alternatively, in MySQL 8.0, you can do this with window functions:
select *
from (
select t.*,
max(status = 'INPROGRESS') over(partition by grp, info) as flag
from mytable t
) t
where flag = 0
Note: group is a language keyword (as in group by), hence not a good choice for a column name; I renamed it to grp in the queries.