GROUP BY ID in query where there are null values for ID

[djhunx]

I have a table of items each having a field called groupID. In the table, there are items having the same groupID but most have null groupIDs. I want to be able to do a SELECT query GROUPING the items by groupID and also want to return all items having null groupIDs. What happens is that all items with null groupIDs are grouped together. Does anyone know how to go around this.? appreciate any help.

 

[swampBoogie]

Hmm

set @x = 0 
select <other columns> , @x := @x - 1
  from t
 group by coalesce(groupid,@x)

Untested so I don't know what happens

 

[djhunx]

tried it.same results. records with null groupids are grouped together.

someone advised to use UNION:
SELECT * FROM table WHERE groupID IS NOT NULL GROUP BY groupID
UNION
SELECT * FROM table WHERE groupID IS NULL


works but only for mysql versions 4.x.x
our server has mysql v3.x.x. so i may have to use two separate queries until we have our mysql upgraded.