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. 1811935 fiddles created (25312 in the last week).

CREATE TABLE historico ( id INT AUTO_INCREMENT PRIMARY KEY, his_status VARCHAR(25) NOT NULL, his_data DATE NOT NULL );
 hidden batch(es)


INSERT INTO historico (his_status, his_data) VALUES ('FRACASSO', '2009-07-12'), ('FRACASSO', '2009-07-15'), ('FRACASSO', '2009-06-12'), ('FRACASSO', '2009-10-12'), ('FRACASSO', '2007-03-12'), ('REAVALIAR', '2005-06-06'), ('REAVALIAR', '2004-07-15'), ('REAVALIAR', '2009-06-12'), ('REAVALIAR', '2017-10-13'), ('REAVALIAR', '2003-04-05'), ('CONFUSAO', '2007-03-12'), ('SUCESSO', '2017-03-02'), ('SUCESSO', '2001-03-06'), ('SUCESSO', '2005-02-11'), ('SUCESSO', '2009-11-12'), ('SUCESSO', '2007-07-07');
 hidden batch(es)


SELECT h.* FROM historico AS h LEFT JOIN historico AS h2 ON h.his_status = h2.his_status AND (h.his_data < h2.his_data OR (h.his_data = h2.his_data AND h.id > h2.id)) WHERE h.his_status IN ('FRACASSO', 'REAVALIAR', 'SUCESSO') GROUP BY h.id HAVING COUNT(*) < 3 ORDER BY h.his_status, h.his_data DESC, h.id;
id his_status his_data
4 FRACASSO 2009-10-12
2 FRACASSO 2009-07-15
1 FRACASSO 2009-07-12
9 REAVALIAR 2017-10-13
8 REAVALIAR 2009-06-12
6 REAVALIAR 2005-06-06
12 SUCESSO 2017-03-02
15 SUCESSO 2009-11-12
16 SUCESSO 2007-07-07
 hidden batch(es)


explain SELECT h.* FROM historico AS h LEFT JOIN historico AS h2 ON h.his_status = h2.his_status AND (h.his_data < h2.his_data OR (h.his_data = h2.his_data AND h.id > h2.id)) WHERE h.his_status IN ('FRACASSO', 'REAVALIAR', 'SUCESSO') GROUP BY h.id HAVING COUNT(*) < 3 ORDER BY h.his_status, h.his_data DESC, h.id;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE h ALL 16 Using where; Using temporary; Using filesort
1 SIMPLE h2 ALL PRIMARY 16 Using where; Using join buffer (flat, BNL join)
 hidden batch(es)


( SELECT * FROM historico WHERE his_status = 'FRACASSO' ORDER BY his_data DESC LIMIT 3 ) UNION ALL ( SELECT * FROM historico WHERE his_status = 'REAVALIAR' ORDER BY his_data DESC LIMIT 3 ) UNION ALL ( SELECT * FROM historico WHERE his_status = 'SUCESSO' ORDER BY his_data DESC LIMIT 3 );
id his_status his_data
4 FRACASSO 2009-10-12
2 FRACASSO 2009-07-15
1 FRACASSO 2009-07-12
9 REAVALIAR 2017-10-13
8 REAVALIAR 2009-06-12
6 REAVALIAR 2005-06-06
12 SUCESSO 2017-03-02
15 SUCESSO 2009-11-12
16 SUCESSO 2007-07-07
 hidden batch(es)


explain ( SELECT * FROM historico WHERE his_status = 'FRACASSO' ORDER BY his_data DESC LIMIT 3 ) UNION ALL ( SELECT * FROM historico WHERE his_status = 'REAVALIAR' ORDER BY his_data DESC LIMIT 3 ) UNION ALL ( SELECT * FROM historico WHERE his_status = 'SUCESSO' ORDER BY his_data DESC LIMIT 3 );
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY historico ALL 16 Using where; Using filesort
2 UNION historico ALL 16 Using where; Using filesort
3 UNION historico ALL 16 Using where; Using filesort
 hidden batch(es)