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

Question

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 to ensure that the GROUP BY clause picks the first ID?

Answer 1

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)

Answer 2

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