Allen_Tsang Allen_Tsang - 4 years ago 92
MySQL Question

how to order result when using group by or distinct?

some dummy data:

id group_id name score
1 A Tom 20
2 B Jone 17
3 B Jack 19
4 C Tim 18


I want to get distinct group_id order by score. Here is my attemp:

select group_id from table order by score
comes out:

group_id
B
C
B
A


so, i think this will work
select distinct group_id from (select group_id from table order by score ) t
, but it comes out:

group_id
A
B
C


the same to
select group_id from (select group_id from table order by score ) t group by group_id
.

however, the expected result is

group_id
B
C
A


I think this should be easy, just need someone show the point.

Answer Source

I believe this is what you want:

select group_id
from table 
group by group_id
order by min(score);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download