CODEWITHSUNDEEP
mysqlmariadb-10.3
Nebur81
Nebur81

Reputation: 29

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?

Upvotes: 1

Views: 116

Answers (3)

steffen
steffen

Reputation: 16958

Your question falls into the category of retrieving the 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;

or

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.

If you want those last (ordered by id) rows where accepted=0, and it doesn't matter that one of the competitors already had an accepted=1 row, just remove the ORDER BY accepted:

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

This would create a partition table, partitioned by competitor, ordered by id and selects each partition's first row (WHERE rn=1) where that row has accepted=0.

Upvotes: 1

Pankaj
Pankaj

Reputation: 2746

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

Upvotes: 0

Jetto Martínez
Jetto Martínez

Reputation: 999

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.

Upvotes: 1

Related Questions