Select a certain amount of data for each status type

Asked

Viewed 176 times

2

Billy Jow here!

On my table historical I have a field status that can assume four possible values:

'EVALUATE', 'FAILURE', 'REEVALUATE', 'SUCCESS'

I wish to select 20 records from each status where the date is most recent. Being that I wish only 'Failure', 'Reevaluate' and 'Success''. What would my query look like to return all fields?

important fields: historical(code, date, status)

mysql sql database query
  • 1

    Ever tried a UNION with 4 SELECT?

  • Yes, it even returned 50 of each, but when I did the query to return all fields of the table it did not work. For some reason returns fewer values for failure.

  • I made an answer, your comment SQL resembles that ?

2 answers

4

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 using the UNION ALL, makes no distinction in the result obtained (distinct) and brings everything that SQL satisfy.

This doubt would be your question, but in the comments need all fields, so:

(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)

References

0


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;

Optimization of SQL code

  • @Virgilionovic The purpose of code optimization is performance. Go back to the other question and read my comments on Antony’s answer.

  • I’ll comment to Virgilionovic for talking nonsense. "The code in the answer you gave I had already done before, but with some kind of error that did not allow the execution, so there is no solution in your answer. You even edited to match my solution (with the parentheses). As for the redundancy of answers in two questions, I believe that this is no crime, because the important thing is to maintain the history so that others can be helped. You should be more concerned about your score on the forum and so are not thinking about the logic of things. - Billly Jow 13/11 at 8:58 pm "

Browser other questions tagged mysql sql database query

You are not signed in. Login or sign up in order to post.