1

I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done.

tb  tbid    ttid    status
-----------------------------
216 6   5   done
216 6   5   running
216 7   5   preparing
217 11  10  done
217 11  10  done
218 12  11  done
218 12  11  running
212 15  14  done
212 15  14  running
220 17  15  done
220 17  15  done

I wanted to have the following rows only:

tb      tbid    ttid    status
--------------------------------
217     11      10      done
220     17      15      done

I have tried the following query but it gives wrong info for tb: 216, because two different tbid.

select tb,tbid,tbid,status FROM tt 
status in ('done')

Could you please help how to get the expected results.

0

Edit

I should not get the result of ttid 5 because it has status as 'preparing'. I should get ttids results only when all the statuses are 'done'.

To get only the ones where done is found twice and no other statuses are found:

SELECT tb,  tbid, ttid,status 
FROM  test t1
WHERE status = 'done'
AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid)
GROUP BY  tb,tbid,ttid,status
HAVING COUNT(*) > 1;

DB-Fiddle

Or

SELECT ttid,status 
FROM test t1
WHERE status = 'done'  
AND NOT EXISTS(SELECT * FROM test t2 where t2.status != 'done' and t1.ttid = t2.ttid)
GROUP BY  ttid,status
HAVING COUNT(*) > 1;

DB-Fiddle

The reasoning for adding it to the group by or applying a function:

if tb or tbid can be different for the same 'done' status and the same ttid, which one do you choose? Do you choose the smallest, the biggest, ...?

An example of what happens if this is true and it is not added to a group by or function:

DB-Fiddle that fails

  • I will try your example, btw I choose biggest one. – Mohan Ragav Mar 1 '19 at 15:37
  • Its not working when two different tbid presented in table. see my DB fiddle: db-fiddle.com/f/759dvP74jxW4HG3rvVi3uD/4 – Mohan Ragav Mar 1 '19 at 16:14
  • @MohanRagav what do you mean not working? It gives a result for ttid 5, 10 and 15 which seems like what you want to me, since 5 has two 'done' statuses. – Randi Vertongen Mar 1 '19 at 17:16
  • I should not get the result of ttid 5 because it has status as 'preparing'. I should get ttids results only when all the statuses are 'done'. – Mohan Ragav Mar 3 '19 at 18:42
  • @MohanRagav alright I think I get what you want, adding an update – Randi Vertongen Mar 3 '19 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.