Search for the maximum number of users and related details

Question

I have a table in which users store ratings and other information about the specified rating (for example, notes on the account or time, etc.). I want the mysql query to find every best user account, as well as related notes and time, etc.

What I tried to use looks something like this:

SELECT *, MAX (grade) FROM table GROUP BY (user)

The problem is that although you can provide users with the best of this query [MAX (rating)], returned notes and time, etc. not related to the maximum score, but a different score (in particular, containing in *). Is there a way to write a query that selects what I want? Or will I have to do this manually in PhP?

Answer 1

You can join the sub-query, as in the following example:

SELECT   t.*,
         sub_t.max_score
FROM     table t
JOIN     (SELECT   MAX(score) as max_score,
                   user
          FROM     table
          GROUP BY user) sub_t ON (sub_t.user = t.user AND
                                   sub_t.max_score = t.score);

The above query can be explained as follows. It starts with:

SELECT t.* FROM table t;

... This in itself will obviously display the entire contents of the table. The goal is to save only lines that represent the maximum score of a particular user. Therefore, if we had the following data:

+------------------------+
| user | score |  notes  | 
+------+-------+---------+
|    1 |    10 |  note a |
|    1 |    15 |  note b |    
|    1 |    20 |  note c |
|    2 |     8 |  note d |
|    2 |    12 |  note e |
|    2 |     5 |  note f |
+------+-------+---------+

... We would like to save only the lines "note c" and "note e".

To find the lines that we want to keep, we can simply use:

SELECT MAX(score), user FROM table GROUP BY user;

, notes , , , , , MAX() GROUP BY. :

• GROUP BY Myths
• MySQL , group by?
• MySql "group by" ?

, . INNER JOIN:

...
JOIN     (SELECT   MAX(score) as max_score,
                   user
          FROM     table
          GROUP BY user) sub_t ON (sub_t.user = t.user AND
                                   sub_t.max_score = t.score);

sub_t. . ON JOIN . , , .

Answer 2

SELECT *
FROM table t
ORDER BY t.score DESC
GROUP BY t.user
LIMIT 1

: , SELECT *

Answer 3

, , . , , .

. , MySQL:

SELECT user, scoredate, score, notes FROM (
    SELECT *, @prev <> user AS is_best, @prev := user
    FROM table1, (SELECT @prev := -1) AS vars
    ORDER BY user, score DESC, scoredate
) AS T1
WHERE is_best

SQL:

SELECT T3.* FROM table1 AS T3
JOIN (
    SELECT T1.user, T1.score, MIN(scoredate) AS scoredate
    FROM table1 AS T1
    JOIN (SELECT user, MAX(score) AS score FROM table1 GROUP BY user) AS T2
    ON T1.user = T2.user AND T1.score = T2.score
    GROUP BY T1.user
) AS T4
ON T3.user = T4.user AND T3.score = T4.score AND T3.scoredate = T4.scoredate

:

1, '2010-01-01 17:00:00', 50, 'Much better'
2, '2010-01-01 14:00:00', 100, 'Perfect score'

, :

CREATE TABLE table1 (user INT NOT NULL, scoredate DATETIME NOT NULL, score INT NOT NULL, notes NVARCHAR(100) NOT NULL);
INSERT INTO table1 (user, scoredate, score, notes) VALUES
(1, '2010-01-01 12:00:00', 10, 'First attempt'),
(1, '2010-01-01 17:00:00', 50, 'Much better'),
(1, '2010-01-01 22:00:00', 30, 'Time for bed'),
(2, '2010-01-01 14:00:00', 100, 'Perfect score'),
(2, '2010-01-01 16:00:00', 100, 'This is too easy');