Get last rows where value is 0

I have a table pesajes where the weigh-ins of the competitors are stored

pesajes:

id  championship   competitor   accepted     time
1       18           1280          0      1370931202  
2       18           1280          1      1370931784 
3       18           1203          0      1370932502  
4       18           870           1      1370934905  
5       18           1203          0      1370961212
6       18           1100          0      1370984288    

I want to select only the rows where the last weigh-in was 0 (accepted is 0), for example: In this case I want to return only the rows 5 and 6

I was using this query:

SELECT * FROM pesajes WHERE championship=18 and accepted=0 GROUP by id

Obviously this query does not work because, among other problems, it returns the row 1 and I do not want that row because the last weigh-in of the competitor 1280 is accepted. How could I solve it?

Pretty much, you need:

• The last record of each competitor with accepted = 0
• Only those competitors that do not have another record with accepted = 1

Other answers use time, so I provide an alternative without using that column. I decided to do this since you don't mention it at all, so I don't know how reliable it could be for what you need.

SELECT p.* 
FROM pesajes p
    JOIN (
        SELECT MAX(id) AS id, SUM(accepted) AS criteria
        FROM pesajes 
        GROUP BY championship, competitor
    ) filter ON filter.id = p.id AND filter.criteria = 0;

This will work in MySQL 5.5 up to 8.0.

And here is the fiddle.