How to order or choose rows in MySQL GROUP BY clause?

I have a table like this:

id       number    otherfields
-------------------------------------------
664      48       aaa
665      49       bbb
666      55       ccc
667      48       ddd

My query groups by the number field, and I want it to pick the first (lowest) id, so that the data comes out like ccc,aaa,bbb (when ordered by number). However I'm getting ccc,ddd,bbb - in other words, it's picking row #667 instead of #664 for the number 48.

Oddly, this only happens on the live server; on localhost I get it the correct way even though the table is exactly the same (exported from localhost, imported onto server).

Is it possible t开发者_StackOverflow中文版o ensure that the GROUP BY clause picks the first ID?

No, it is not possible in MySQL. You have to use a join.

SELECT id, number, otherfields FROM table 
  WHERE id in (SELECT min(id) FROM table GROUP BY number)

SQL-92 version.

SELECT 
     id, number, otherfields 
FROM 
     table t
     join (SELECT min(id) as id, number FROM table GROUP BY number) sq --subquery
       on t.id = sq.id