views:

204

answers:

2

I have a table storing transactional data for users. To find a users rating you take the average score of the last 10 entries for that user. Is there a way to get that with SQL?

I need to be able to work it out for a single user given their ID. And to get a list of all users ordered by their score.

Currently I'm working it out outside MySQL and storing it in another col for each user so I can ORDER_BY that.

# returns average for all transactions.
SELECT user_id, AVG(score) FROM transactions WHERE user_id = 1
# returns scores for last 10 transactions.
SELECT user_id, score FROM transactions WHERE user_id = 1 ORDER_BY date DESC LIMIT 10
A: 

Just combine the two queries already in use:

SELECT user_id, AVG(score)
FROM transactions
WHERE rowid in (
    SELECT rowid
    FROM transactions
    WHERE user_id = 1
    ORDER_BY date DESC
    LIMIT 10)

where rowid is whatever the identifying I.D. is.

wallyk
Jake
A: 

Use:

  SELECT x.user_id,
         AVG(x.score)
    FROM (SELECT t.user_id,
                 t.score
            FROM TRANSACTIONS t
           WHERE t.user_id = ?
        ORDER BY t.date
           LIMIT 10) x
GROUP BY x.user_id
OMG Ponies
Success! So you SELECT FROM a subquery instead of a table. I'll try to figure out how to get scores for all players, I guess it's the done thing to ask a separate question about that if I need to.
Jake
The only way to perform this for all users is to use ranking, which MySQL does not have. You can use a variable to provide psuedo ranking.
OMG Ponies