CODEWITHSUNDEEP
mysqlstored-procedures
Varun Jain
Varun Jain

Reputation: 1911

Order by Select into variable mysql

I have the following sql query:

Select
sum(
    (IF(notification_type = 'qAccepted',credit*0.5,0)) + 
    (IF(notification_type = 'creditScored',credit,0)) +
    (IF(notification_type = 'flagPositive',credit,0))
) into b_credit
from notifications n
left join user_answers ua on ua.question_id = n.question_id and ua.user_id = n.user_id 
left join user_credits uc on uc.user_id = n.user_id
where (unix_timestamp() - timestamp) < 7*86400  
group by n.user_id  
order by b_credit desc 
limit 1;

How can I order by the b_credit variable?

Edit This makes the b_credit variable NULL. The procedure runs fine.

Upvotes: 0

Views: 77

Answers (1)

Barmar
Barmar

Reputation: 782785

Try this:

SELECT MAX(x_credit) INTO b_credit
FROM (
    Select
    sum(
        (IF(notification_type = 'qAccepted',credit*0.5,0)) + 
        (IF(notification_type = 'creditScored',credit,0)) +
        (IF(notification_type = 'flagPositive',credit,0))
    ) AS x_credit
    from notifications n
    left join user_answers ua on ua.question_id = n.question_id and ua.user_id = n.user_id 
    left join user_credits uc on uc.user_id = n.user_id
    where (unix_timestamp() - timestamp) < 7*86400
    group by n.user_id
) x

Upvotes: 2

Related Questions