Question: I am trying to query a dataset from a single table, which contains quiz answers/entries from multiple users. I want to pull out the highest scoring entry from each individual user.

My data looks like the following:

```
ID  TP_ID               quiz_id name                num_questions   correct     incorrect   percent     created_at
1   10154312970149546   1       Joe                 3               2           1           67          2015-09-20 22:47:10
2   10154312970149546   1       Joe                 3               3           0           100         2015-09-21 20:15:20
3   125564674465289     1       Test User           3               1           2           33          2015-09-23 08:07:18
4   10153627558393996   1       Bob                 3               3           0           100         2015-09-23 11:27:02

```

My query looks like the following:

```
SELECT *    FROM `entries` 
WHERE       `TP_ID` IN('10153627558393996', '10154312970149546') 
GROUP BY    `TP_ID` 
ORDER BY    `correct` DESC

```

In my mind, what that should do is get the two users from the `IN` clause, order them by the number of `correct` answers and then group them together, so I should be left with the 2 highest scores from those two users.

In reality it's giving me two results, but the one from Joe gives me the lower of the two values (2), with Bob first with a score of 3. Swapping to ASC ordering keeps the scores the same but places Joe first.

So, how could I achieve what I need?

Answer: 
You're after the [groupwise maximum](https://dev.mysql.com/doc/en/example-maximum-column-group-row.html), which can be obtained by joining the grouped results back to the table:

```
SELECT * FROM entries NATURAL JOIN (
    SELECT   TP_ID, MAX(correct) correct
    FROM     entries
    WHERE    TP_ID IN ('10153627558393996', '10154312970149546')
    GROUP BY TP_ID
) t

```

Of course, if a user has multiple records with the maximal score, it will return all of them; should you only want some subset, you'll need to express the logic for determining which.
MySql is quite lax when it comes to group-by-clauses - but as a rule of thumb you should try to follow the rule that other DBMSs enforce:
In a group-by-query each column should either be part of the group-by-clause or contain a column-function.

For your query I would suggest:

```
SELECT `TP_ID`,`name`,max(`correct`)    FROM `entries` 
WHERE       `TP_ID` IN('10153627558393996', '10154312970149546') 
GROUP BY    `TP_ID`,`name` 

```

Since your table seems quite denormalized the `group by name`-par could be omitted, but it might be necessary in other cases.

`ORDER BY` is only used to specify in which order the results are returned but does nothing about what results are returned - so you need to apply the `max()`-function to get the highest number of right answers.