Search code examples
mysqlsql-order-byranking

mysql trying to rank rows by order by

I'd like to calculate a ordered-by rank for my rows. It isn't calculating correctly- I'm only getting a null and 1. I expect to get a rank of 1, 2, 3... based on my joins/order by ladder_name_lh2 = ladder_name_lh1 and date_trigger_event_lh2, and member_id_lh1.

Basically, I am trying to say If ladder_name_2 matches ladder_name_1 (this is a before/after set up - ladder 2 comes after ladder 1, and there are 6 ladder names (categories), but in order to tell that they're related, I want to make sure that the rank is only being generated if the categories for before and after match) and then generate a rank based on date_2, member_id.

I've written the following: 

select 
        cte_ladder_history_self_join_lh1.member_id_lh1,
        cte_ladder_history_self_join_lh1.ladder_config_id_lh1,
        cte_ladder_history_self_join_lh1.trigger_name_lh1,
        cte_ladder_history_self_join_lh1.trigger_record_id_lh1,
        cte_ladder_history_self_join_lh1.date_trigger_event_lh1,
        cte_ladder_history_self_join_lh1.ladder_name_lh1,
        cte_ladder_history_self_join_lh1.ladder_value_lh1,
        cte_ladder_history_self_join_lh1.ladder_change_lh1,
        cte_ladder_history_self_join_lh2.member_id_lh2,
        cte_ladder_history_self_join_lh2.trigger_name_lh2,
        cte_ladder_history_self_join_lh2.trigger_record_id_lh2,
        cte_ladder_history_self_join_lh2.date_trigger_event_lh2,
        cte_ladder_history_self_join_lh2.ladder_name_lh2,
        cte_ladder_history_self_join_lh2.ladder_value_lh2,
        cte_ladder_history_self_join_lh2.ladder_change_lh2,
        datediff(cte_ladder_history_self_join_lh2.date_trigger_event_lh2,cte_ladder_history_self_join_lh1.date_trigger_event_lh1) as days_spent,
        @member_id:=member_id_lh2 AS member_id,
        @row_number:=(CASE
                WHEN @member_id = member_id_lh1
                THEN @row_number + 1
                ELSE 1
            END) AS rank 

    from 
        cte_ladder_history_self_join_lh1 #previous or current ladder history 
            left join cte_ladder_history_self_join_lh2 on cte_ladder_history_self_join_lh1.member_id_lh1 = cte_ladder_history_self_join_lh2.member_id_lh2
                and cte_ladder_history_self_join_lh2.ladder_name_lh2 = cte_ladder_history_self_join_lh1.ladder_name_lh1
                and cte_ladder_history_self_join_lh1.ladder_value_lh1 <> cte_ladder_history_self_join_lh2.ladder_value_lh2
                and cte_ladder_history_self_join_lh1.date_trigger_event_lh1 < cte_ladder_history_self_join_lh2.date_trigger_event_lh2 # lh2 shows second begin date / end date for lh if exists
        order by date_trigger_event_lh2 desc, member_id_lh1 desc;

Which returns the following: enter image description here

I'm not sure why the ranking isn't working correctly. I'd expect there to be a rank from about 1 - 20 (based on the number of rows that match the ladder_name = advocacy criteria for this person) but I'm getting some wildly different numbers.

Thoughts?

Solution

  • Fixed teh ranking code to read: 

    CREATE TEMPORARY TABLE cte_leenk_ladder_history_rank
    SELECT * ,
    @row_number:=(CASE
    WHEN @member_id = member_id
    THEN @row_number + 1
    ELSE 1
    END) AS rank,
    @member_id := member_id

    FROM 
    cte_leenk_ladder_history_order
    where ladder_change = 1
    order by salesforce_id desc, date_trigger_event desc;