Get last rows where value is 0-CodePudding

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?

CodePudding user response：

Use NOT EXISTS:

SELECT p1.* 
FROM pesajes p1
WHERE p1.championship = 18 AND p1.accepted = 0
  AND NOT EXISTS (
                   SELECT *
                   FROM pesajes p2
                   WHERE p2.championship = p1.championship 
                     AND p2.time > p1.time 
                     AND p2.accepted <> p1.accepted
                 );

See the demo.

CodePudding user response：

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.

CodePudding user response：

Query -

select * from champ c1 inner join 
(select competitor, max(time_1) mtime from champ
group by competitor) c2
where c1.competitor = c2.competitor
and c1.time_1 >= c2.mtime
and c1.accepted = 0

Fiddle here

CodePudding user response：

Your question falls into the category of retrieving the greatest-n-per-group rows. Beginning with MySQL 8 or MariaDB 10.2, you can use a partitioning non-aggregating Window Function:

WITH tmp AS (
    SELECT p.*, ROW_NUMBER() OVER (
        PARTITION BY competitor ORDER BY accepted DESC, id DESC) AS rn
        FROM pesajes AS p
)
SELECT * FROM tmp WHERE accepted = 0 AND rn = 1;

SELECT * FROM (
    SELECT p.*, ROW_NUMBER() OVER (
        PARTITION BY competitor ORDER BY accepted DESC, id DESC) AS rn
        FROM pesajes AS p
) as tmp
WHERE accepted = 0 AND rn = 1;

These queries create a partition for each competitor, ordered by accepted, then id. Finally, only the first (order by id with rn=1) rows are returned which "still" have accepted=0.