Order by, Group by...

Fearless_Fish

Hi,

I've trawled through dozens of google pages and MySQL documentation to no avail for this problem, so my apologies if the solution is simple!

I have two columns within a table that I want to sort by. The first column is "Stat" which has options (Assigned, Unassigned, Awaiting Parts, On Hold, Completed) and the second is a simple ID field.

I need to order via the stat column first - but not in alphabetical order - the order must be:
-Unassigned
-Assigned
-Awaiting Parts
-On Hold
-Completed

The only way I've been able to get it to work is alphabetically, which is no good :queasy:

Any thoughts?

justsomeone

There's no way to arbitrarily sort stat the way you want.

There are some options.

1
You could create a new table to store the available stats and associate an id with each - 0 for Unassigned, 1 for Assigned ... and so on. Then replace your current stat values with the appropriate id. Now you can sort based on the new numeric value which will give you ordering you want.

2.
Do multiple queries. If you want "Unassigned" to appear first, so a search for only those orders with a stat of "Unassigned", and display those. Then search for "Assigned", and display those, and so on...

Fearless_Fish

Yes, that seems sensible. It seems I've been bogged down looking for some sort of super query that I didn't look at it logically :bemused:

Thanks very much, I'll crack on with one of those options!

Roger_Ramjet

Definitely use option 1, it's the only way to go.

justsomeone

Just one piece of finesse. Rather than using 0,1,2,3... as the identifiers for your stat table, maybe use 0, 10,20,30...

The advantage of this is that if a new status comes up as a requirement, which you want to appear between two existing ones, you can create an id between the existing ones.

Fearless_Fish

Ah, I see we have a perfectionist in the house. It will be like a work of art 🙂