Group By and Order by Help

[ptmuldoon]

I'm trying to query all fields in a table, and then have the table Group by a 'state' field, and then have each grouping in descending order by its 'time' field.

But I also want to list the Groups in a specific order, although the 'state' field is not in a alpha or numeric sequence, but rather something like waiting, initial, playing, finished.

Can this be done? I'm trying to learn about how to use Group by and Order by, but not sure if thats the best approach.

[guelphdad]

I don't think you want GROUP BY at all. THink of GROUP BY as collapsing your results common to a group to a single row.

Say you have members of the Osmonds, the Jackson Five and the BeeGees and you only want to count the members of those families, you end up with three rows, one for each family and the count.


select
familyname,
count(*) as total
from datable
group by
familyname


it doesn't sound like that is what you want.

as to your ordering you can use

ORDER BY field(state,'waiting','initial','playing','finished')
for example.

if it is more than that then you will have to show some sample rows.