Mysql picking the wrong id when using group by.
If I have a table test with 3 columns id, name, count and has following values.
mysql> Select * from test;
+------+-------+-------+
| id | name | count |
+------+-------+-------+
| 1 | david | 10 |
| 2 | david | 5 |
| 3 | david | 3 |
| 4 | david | 40 |
| 5 | navi | 6 |
| 6 | navi | 5 |
| 7 | navi | 29 |
| 8 | navi | 10 |
+------+-------+-------+
8 rows in set (0.00 sec)
Here I want to group by the name and in that I want the id of MIN(count). So I tried the below query.
mysql> Select id,name,MIN(count) from test GROUP BY name;
+------+-------+------------+
| id | name | MIN(count) |
+------+-------+------------+
| 1 | david | 3 |
| 5 | navi | 5 |
+------+-------+------------+
2 rows in set (0.00 sec)
The actual result of id for count=3 is 3 and for count=5 is 6, But id shown here is incorrect. So kindly anyone help to achive this use case.
Expected Output :
+------+-------+------------+
| id | name | MIN(count) |
+------+-------+------------+
| 3 | david | 3 |
| 6 | navi | 5 |
+------+-------+------------+
The above shown already asked question are different with my question