clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1451362 fiddles created (18695 in the last week).

CREATE TABLE cte1 ( `instance` INT, `version` DECIMAL(5,1), `cnt` INT);
 hidden batch(es)


INSERT INTO cte1 VALUES ( 1021 , 18.1 , 263), ( 1021 , 18.2 , 422), ( 1021 , 19.1 , 949), ( 1191 , 18.2 , 28 ), ( 1195 , 18.1 , 584), ( 1195 , 18.2 , 176), ( 1195 , 18.3 , 437), ( 1195 , 19.1 , 152), ( 1195 , 19.2 , 545), ( 1195 , 19.3 , 399), ( 1196 , 18.3 , 844), ( 1196 , 19.1 , 800), ( 1197 , 18.3 , 2 ), ( 1201 , 18.1 , 471), ( 1201 , 18.2 , 584), ( 1201 , 18.3 , 553), ( 1201 , 19.1 , 498), ( 1201 , 19.2 , 203), ( 1201 , 19.3 , 36), ( 1208 , 18.1 , 444), ( 1208 , 18.2 , 548), ( 1208 , 18.3 , 31), ( 1208 , 19.2 , 357), ( 1210 , 19.1 , 514), ( 1211 , 18.2 , 341), ( 1211 , 19.1 , 531);
 hidden batch(es)


WITH /* cte1 AS ( SELECT instance, version, COUNT(id) AS cnt FROM actions WHERE status=0 AND version IS NOT NULL GROUP BY instance, version ), */ cte2 AS ( SELECT instance, MAX(version) version FROM cte1 GROUP BY instance ), cte3 AS ( SELECT instance, MAX(version) version FROM cte1 LEFT JOIN cte2 USING (instance, version) WHERE cte2.instance IS NULL GROUP BY instance ) SELECT cte1.* FROM cte1 JOIN cte3 USING (instance, version);
instance version cnt
1021 18.2 422
1195 19.2 545
1196 18.3 844
1201 19.2 203
1208 18.3 31
1211 18.2 341
 hidden batch(es)