Find first user to reach goal

An interesting one, I think :) - Users in my app get points for actions they perform.

I wish to find the first user to reach a goal of 100 points.

One method to do that is to return all performed actions, with their date and number of points, and run through them in PHP to find the first user who reached the goal, but I was wondering if this can 开发者_运维问答be performed in SQL.

I know how to return all users who reached to goal to a specific date, but any thoughts on how to find the first one who reached the goal?

Thanks in advance,

Dorian

Create a query that computes the cumulative points scores. You then just need the lowest date with a cumulative point score of at least 100.

you could create a cache table (the data in this table is completely redundant so it can be discarded and recreated as needed)

in this table store the user (id), the date of each "event" that added points to this users balance, and the ballance itself ... like a bank balance for every user ...

the table can be recreated from the stored events/actions since it is a sum-history grouped by the user ... or it can be updated with every new event/Action ...

Create a column in the MySQL table, namely FIRST.

The first query that meets a certain condition, say points = 500, will set the 'FIRST' value in the table to 1. Every other query will have FIRST set to 0.

Example:

ID | USER | POINTS | FIRST
1    dor    480       0
2    tim    500       1
3    mit    200       0

then search for FIRST=1

$q = mysql_query("SELECT * FROM table WHERE FIRST='1'");