SQL's Confusion on picking ID in group by

35 Views Asked by At

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

0

There are 0 best solutions below