Sorting results

[zzz]

It's got to be quite simple, but I'm stuck.

I'd like to be able to sort output in the following order:

1. by Alphapet first a-z
2. then anything that starts with numbers and
3. then anyhting that starts with _ (underscore) or other characters...

[bradgrafelman]

Try something like this:

ORDER BY
	CASE searchCol
		WHEN REGEXP '^[a-z]' THEN 1
		WHEN REGEXP '^[0-9]' THEN 2
		ELSE 3
	END CASE, searchCol

[dagon]

sometimes its worth having a 'sort' column in the db for custom sort issues.

[zzz]

oh, I should've looked at my code before I posted the question. It is already quite complex. He's what I have at the moment:

ORDER BY amount DESC,
	COALESCE(falias,fname),
	COALESCE(lalias,lname)

Not sure, how would I incorporate the CASE...

[bradgrafelman]

zzz;10967927 wrote:

Not sure, how would I incorporate the CASE...

Same as you would if it were just another column name.

[zzz]

I'm not sure I understand. It is a list of names essentially. Most have no amount value, but those that do are placed on the top of results.

The names have 2 values, first and last, but if the alias is present then it takes priority over the corresponding name. The sort is basically done by last name.