Billy Jow here!
In my historical table I have a status field that can take four possible values:
'EVALUATE', 'FAIL', 'REVOK', 'SUCCESS'
I want to select 20 records of each status where the date is most recent. Since I want only the statuses 'Failure', 'Revaluate' and 'Success' . How would I get my query to return all the fields?
important fields: historical (code, date, status)
SELECT h.*
FROM historico h LEFT JOIN historico h2
ON h.his_status = h2.his_status
AND (h.his_data < h2.his_data OR (h.his_data = h2.his_data AND h.his_codigo > h2.his_codigo))
WHERE h.his_status IN ('FRACASSO', 'REAVALIAR', 'SUCESSO')
GROUP BY h.his_codigo
HAVING COUNT(*) < 50
ORDER BY h.his_status, h.his_data DESC, h.his_codigo;
Use the command SQL UNION ALL example :
(SELECT codigo, data, status
FROM historico WHERE status = 'AVALIAR' ORDER BY DATA DESC LIMIT 20)
UNION ALL
(SELECT codigo, data, status
FROM historico WHERE status = 'FRACASO' ORDER BY DATA DESC LIMIT 20)
UNION ALL
(SELECT codigo, data, status
FROM historico WHERE status = 'REAVALIAR' ORDER BY DATA DESC LIMIT 20)
UNION ALL
(SELECT codigo, data, status
FROM historico WHERE status = 'SUCESSO' ORDER BY DATA DESC LIMIT 20)
When you use UNION ALL , make no distinction in the result obtained (< > ) and brings all that SQL satisfies.
This question would be your question, but in the comments you need all fields, then:
(SELECT * FROM historico WHERE status = 'AVALIAR' ORDER BY DATA DESC LIMIT 20)
UNION ALL
(SELECT * FROM historico WHERE status = 'FRACASO' ORDER BY DATA DESC LIMIT 20)
UNION ALL
(SELECT * FROM historico WHERE status = 'REAVALIAR' ORDER BY DATA DESC LIMIT 20)
UNION ALL
(SELECT * FROM historico WHERE status = 'SUCESSO' ORDER BY DATA DESC LIMIT 20)