How can I make this query?

Question

This is the table for testing

----------------------------------------------
id | username | point | level | created_date
----------------------------------------------
 1 | name_a   |  1    |   1   | 2011-08-01
 2 | name_a   |  2    |   2   | 2011-08-02
 3 | name_b   |  5    |   1   | 2011-08-02
 3 | name_c   |  6    |   1   | 2011-08-02
 4 | name_d   |  1    |   1   | 2011-08-01
 5 | name_d   |  3    |   1   | 2011-08-02
 5 | name_d   |  5    |   2   | 2011-08-03
 4 | name_e   |  5    |   1   | 2011-08-01
 5 | name_e   |  5    |   2   | 2011-08-02
 5 | name_e   |  5    |   3   | 2011-08-03
----------------------------------------------

Requirement for the query is to query (as much as possible in one query) the username, point of the table.

1. Order by sum of user's score on each level.
2. If user have 2 score on the same level, it will only get the latest score.
3. Group by username
4. total score must be less than 10
5. maximum of the score per level is 5

Output sample:

--------------------
 username  | tpoint|
--------------------
  name_d   |  8    |
  name_b   |  5    |
  name_a   |  3    |
--------------------

name_e and name_c was ignored.

Answer 1

Sounds like a fun query!

SELECT username, SUM(point) AS points
FROM (SELECT username, level, point
      FROM (SELECT username, level, LEAST(point, 5) AS point
            FROM table
            WHERE points <= 5
            ORDER BY created_date DESC) AS h
      GROUP BY username, level) AS h2
GROUP BY username
HAVING points < 10
ORDER BY points DESC

This should do it! Just replace "table".

EDIT:

Do you want to exclude rows which got a score over 5, or have the value as 5? Just remove WHERE points <= 5 if such.

Answer 2

SELECT SUM(t3.point) AS tpoint, t3.username
FROM (
    SELECT t1.level, t1.username, t1.created_date, t1.point
    FROM testing AS t1
    INNER JOIN (SELECT level, username, MAX(created_date) AS MaxDate
                FROM testing) AS t2
          ON (t1.level=t2.level AND t1.username=t2.username AND t1.created_date = t2.MaxDate)
    WHERE t1.point <= 5
    ) AS t3
GROUP BY t3.username
HAVING tpoint < 10
ORDER BY tpoint DESC

Don't know if I used the aliases correctly, hope this works!

The inner query with the join is to get the latest username,level combination where single level point count is > 5. This is then used to get the total sum per username and discard those with more then 10 points.