sql rank row results

Question

I"m trying to add a new col that shows the rank (or sequence) of row results by date.

I've written:

 SELECT 
@row_number:=(CASE
    WHEN @member_id = lh.member_id and lc.ladder_advocacy is not null 
        THEN @row_number + 1
    when @member_id = lh.member_id and lc.ladder_advocacy is null then "null"
    ELSE 1 /* there is an error here - i need it to return a 1 if not null, then 2 for the 2nd instance, etc */ 
END) AS rank_advocacy,
@member_id:=lh.member_id AS member_id,
lh.ladder_change,
lc.name,
lc.ladder_advocacy,
lc.ladder_elected,
lc.ladder_policy,
lc.ladder_engagement,
lc.ladder_newventure,
lc.ladder_collective,
lc.is_trigger
FROM
leenk_ladder_history AS lh
    LEFT JOIN
leeds_so.leenk_ladder_config AS lc ON lh.ladder_config_id = lc.id
WHERE
ladder_change = 1 AND trigger_active = 1
ORDER BY member_id, trigger_event_date DESC; 

There is an error at row 4, and I'm not sure how to fix it. For the first result, I want to return 1. for the second results, I want to return @row_number + 1. Third result, @row_number+2 (etc).

How do I achieve this?

Answer 1

I don't understand how the condition lc.ladder_advocacy is not null is being used. However, the basic structure is:

SELECT (@row_number = IF(@member_id = lh.member_id, @row_number + 1
                         IF(@member_id := lh.member_id, 1, 1)
                        )
       ) as rank_advocacy,
       lh.ladder_change,
       . . .

Some really important points:

• You need to assign @member_id and @row_number in the same expression. MySQL (as with all other databases) does not guarantee the order of evaluation of expressions.
• In more recent versions of MySQL, I think the ORDER BY needs to go in a subquery, with the variable expressions in the outer query.