MariaDB 10.2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE historico (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    his_status VARCHAR(25) NOT NULL, 
    his_data DATE NOT NULL
);
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');
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


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
null
null
null
null
16
Using where; Using temporary; Using filesort

1
SIMPLE
h2
ALL
PRIMARY
null
null
null
16
Using where; Using join buffer (flat, BNL join)


(
    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


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
null
null
null
null
16
Using where; Using filesort

2
UNION
historico
ALL
null
null
null
null
16
Using where; Using filesort

3
UNION
historico
ALL
null
null
null
null
16
Using where; Using filesort

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

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	h	ALL	null	null	null	null	16	Using where; Using temporary; Using filesort
1	SIMPLE	h2	ALL	PRIMARY	null	null	null	16	Using where; Using join buffer (flat, BNL join)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	historico	ALL	null	null	null	null	16	Using where; Using filesort
2	UNION	historico	ALL	null	null	null	null	16	Using where; Using filesort
3	UNION	historico	ALL	null	null	null	null	16	Using where; Using filesort