CODEWITHSUNDEEP
mysqlselect
ntg
ntg

Reputation: 61

mysql select only non canceled rows

I have the following data:

id  vote_id  user_id  status 
1   1        1        1         the vote is active 
2   1        1        2         vote is canceled 
3   1        1        1         vote is active again

status is always 1 at first, then if status is 2 the vote is canceled. On the third row the user acts again and its status is active. I need to be able to select a row only if it doesn't have a canceling row afterwards

Considering that I need to do this not only for 1 vote_id per user, how can I get all rows for all users that are still active!?

The full data sample will look like this: 

id  vote_id  user_id  status 
1   1        1        1         - vote1 is active 
2   1        1        2         - vote1 is canceled 
3   1        1        1         - vote1 is active again 
4   2        1        1         - vote2 is active 
5   2        1        2         - vote2 is canceled

I would need only row 3 here because (1-2) and (4-5) cancel each other out.

Upvotes: 0

Views: 261

Answers (2)

LSerni
LSerni

Reputation: 57418

Given the situation

id, vote_id, user_id, status 
1,1,1,1 - the vote is active 
2,1,1,2 - vote is canceled 
3,1,1,1 - vote is active again

I assume that the system does not allow for a vote to be canceled unless that vote is active, nor to reactivate unless it is in canceled state.

Therefore, if the number of "state 1"'s for a vote is equal to the number of "state 2"'s, the vote is canceled; otherwise it is active.

Under this hypothesis,

SELECT id, vote_id, user_id, active AS status FROM
    ( SELECT MAX(id) AS id,
           SUM(CASE WHEN status = 1 THEN 1 WHEN status = 2 THEN -1 ELSE 0 END)
              AS active,
           user_id,
           vote_id
    FROM votes GROUP BY user_id, vote_id ) AS votes
WHERE votes.status = 1;

or also - I don't like this because of status name ambiguity -

SELECT MAX(id) AS id,
    SUM(CASE WHEN status = 1 THEN 1 WHEN status = 2 THEN -1 ELSE 0 END) AS status,
    user_id,
    vote_id
FROM votes GROUP BY user_id, vote_id
HAVING status = 1;

If you are sure of the status values, and there are no others, you could also try

SUM((1-status)*2+1) AS status

but I don't know if the speed gain (if any!) can make up for the lack of clarity.

Upvotes: 1

Becuzz
Becuzz

Reputation: 6857

I came up with this

select t.*
from @temp t
left join (select max(id) as lastCanceledId, vote_id
            from @temp
            where status = 2
            group by vote_id) cancels on t.vote_id = cancels.vote_id
where COALESCE(cancels.lastCanceledId, 0) < t.id
and t.Status = 1

where @temp is the name of your table. The select in the middle finds the last canceled row for a given vote_id. The outer part filters out all rows that came before the canceling row. This assumes that the ids are indicative of the order things happened in.

Upvotes: 0

Related Questions