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+')