Ordering List of Members Closest To Their Next Award

Question

I have two tables, one 'gift_limit_count' and one 'score' as follows:

'gift_limit_count' containing:

gift_id
gift_limit
gift_amount

'score' containing:

user_id
user_name
user_score

When a user's 'user_score' count gets to the next highest 'gift_limit' they are awarded with the 'gift_amount'. What i'd like to do is show a list of say, the top 10 users closest to their next award.

For example:

David(user_name) has 1 point to go before being awarded with £40 (gift_amount)
Suzi has 2 points to go before being awarded with £20
Ian has 2 points to go before being awarded with £40
Zack has 3 points to go before being awarded with £30
...
...

score 2 · Accepted Answer · answered Apr 26 '12 at 17:28

Select S.user_id, S.user_name, S.user_score
    , GLC.gift_limit, GLC.gift_amount
From    (
        Select S1.user_id
            , Min( GLC1.gift_limit ) As NextLimit
        From score As S1
            Join gift_limit_count As GLC1
                On GLC1.gift_limit > S1.user_score
        Group By S1.user_id
        ) As Z
    Join score As S
        On S.user_id = Z.user_id
    Join gift_limit_count As GLC
        On GLC.gift_limit = Z.NextLimit
Order By ( gift_limit - user_score )
Limit 10

score 1 · Answer 2 · answered Apr 26 '12 at 17:20

1

You might be able to do a query with a join based on min( score - gift_limit ) ( with score < gift_limit ) and then sort by the lowest value for each person. Group it to get one answer per person too.

answered Apr 26 '12 at 17:20

ethrbunny

10,379
9
69
131

Ordering List of Members Closest To Their Next Award

2 Answers2