Page 1 of 1

MySQL custom sort

Posted: Wed Apr 27, 2005 5:08 pm
by Todd_Z
How can i include into a mysql select sql a custom sort... for example

A+, A, A-, A-/B+, B+, B, B-, etc.

And also, I would like to alphabetize results from a sql based on the string after a possible (60)... for example:

Hannibal
(60) Hero
Hybrid Theory

Posted: Wed Apr 27, 2005 11:19 pm
by Skara
I'd sort them after fetching them. I'd also store the (60) as a separate boolean column.
Will that not work?

Posted: Thu Apr 28, 2005 5:33 am
by Todd_Z
It's true, it would work -> It's also a good idea, I may convert them.

About the sort after the fetch, I have a sort method at the moment, but I feel like it could be a lot faster in the mysql query itself.

Posted: Thu Apr 28, 2005 5:54 am
by onion2k
Store the mark in a seperate table and use a join to get the text. That way your mark's will be INTs, and easily sortable. EG

Code: Select all

Results table:
RESULT_ID NAME
1         A+
2         A
3         A-
4         B+

Marks table:
ID NAME         RESULT_ID
1  John Smith   2
2  Fred Bloggs  4
3  Freda Jones  1

SQL:
select m.*, r.name as result_name from marks m left join results r on m.result_id = r.result_id order by m.result_id

ID NAME         RESULT_ID RESULT_NAME
3  Freda Jones  1         A+
1  John Smith   2         A
2  Fred Bloggs  4         B+

Posted: Fri Apr 29, 2005 5:18 am
by timvw
might want to try

Code: Select all

SELECT *
FROM foo
ORDER BY FIELD(column, 'A+', 'A-', 'B+')