fork download
copy 
  1. CREATE TABLE TestTable (ID INT, CONTRACT_ID VARCHAR(100), STAT_NEW  VARCHAR(100), UPD_DT  DATETIME);
  2.  
  3. INSERT INTO TestTable VALUES (0   ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
  4. INSERT INTO TestTable VALUES (1   ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
  5. INSERT INTO TestTable VALUES (2   ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
  6. INSERT INTO TestTable VALUES (3   ,'CR 01 to MWO 1','Sent to VMO','12-AUG-13');
  7. INSERT INTO TestTable VALUES (4   ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
  8. INSERT INTO TestTable VALUES (5   ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
  9. INSERT INTO TestTable VALUES (6   ,'CR 01 to MWO 1','Sent to VMO','12-AUG-13');
  10. INSERT INTO TestTable VALUES (7   ,'CR 01 to MWO 2','Sent to xxxx Project Team','11-AUG-13');
  11. INSERT INTO TestTable VALUES (8   ,'CR 01 to MWO 2','Sent to xxxx Project Team','11-AUG-13');
  12. INSERT INTO TestTable VALUES (9   ,'CR 01 to MWO 2','Sent to xxxx Project Team','11-AUG-13');
  13. INSERT INTO TestTable VALUES (10  ,'CR 01 to MWO 2','Sent to VMO','12-AUG-13');
  14. INSERT INTO TestTable VALUES (11  ,'CR 01 to MWO 3','Sent to xxxx Project Team','12-AUG-13');
  15. INSERT INTO TestTable VALUES (12  ,'CR 01 to MWO 3','Sent to xxxx Project Team','12-AUG-13');
  16. INSERT INTO TestTable VALUES (13  ,'CR 01 to MWO 3','Sent to VMO','13-AUG-13');
  17.  
  18.  
  19. SELECT CONTRACT_ID,COUNT(STAT_NEW) STAT_NEW_COUNT FROM
  20. (
  21.     SELECT t.*
  22. 		,(SELECT COUNT(id) FROM TestTable WHERE STAT_NEW='Sent to VMO' AND ID < t.ID) AS cnt
  23. 	from TestTable t
  24. 	WHERE STAT_NEW<>'Sent to VMO'
  25. ) tt
  26. GROUP BY CONTRACT_ID,cnt;
  27.  
Success #stdin #stdout 0s 2964KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
CR 01 to MWO 1|3
CR 01 to MWO 1|2
CR 01 to MWO 2|3
CR 01 to MWO 3|2
https://ideone.com/mkJtrg
language:
SQLite (sqlite 3.27.2)
created:
10 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!