Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hello and thanks for taking time to read this question.

I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific criteria to this column. For example, to the following table, I want to ORDER BY 'group', showing first the 9,7,6 'group' items and, in the end 10,8,5 'group' items:

names     group
--------- ------
susanita  10
miguelito 5
mafalda   7
manolito  8
libertad  6
felipe    9
guille    8

thanks in advance.

share|improve this question
    
This was a good puzzle. I am sure many have dealt with situation. So, +1 for bringing it to the table today !!! –  RolandoMySQLDBA Sep 6 '11 at 0:36
add comment

4 Answers

up vote 4 down vote accepted
SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');

I took your sample data, loaded it into a table called mytable and ran it.

Here are the results:

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)

mysql> create table mytable
    -> (
    ->    names varchar(10),
    ->    `group` int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into mytable values
    -> ('susanita',  10),
    -> ('miguelito', 5),
    -> ('mafalda',   7),
    -> ('manolito',  8),
    -> ('libertad',  6),
    -> ('felipe',    9),
    -> ('guille',    8);
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+-----------+-------+
| names     | group |
+-----------+-------+
| susanita  |    10 |
| miguelito |     5 |
| mafalda   |     7 |
| manolito  |     8 |
| libertad  |     6 |
| felipe    |     9 |
| guille    |     8 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM mytable ORDER BY
    -> LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

UPDATE 2011-09-06 12:33 EDT

Here is another approach:

SELECT* FROM mytable ORDER BY
IF(FIELD(`group`,9,7,6,10,8,5)=0,99999,FIELD(`group`,9,7,6,10,8,5));

This will force any groups other than 9,7,6,10,8,5 to appear at the very bottom of the query.

UPDATE 2011-09-06 14:39 EDT

mysql> SELECT names, `group`
    -> FROM mytable
    -> WHERE `group` IN (9,7,6,10,8,5)
    -> ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.00 sec)

Hey @Nick, yours works as well against my sample data !!!

share|improve this answer
    
+1 That is an interesting approach. –  Derek Downey Sep 5 '11 at 23:02
    
Hello Rolando, thanks for your answer, it works for me if the data type of 'group' is 'int' or 'varchar', and if the group have similar text (for example text9, text7, text6, text10, text8, text5). thankyou! powerfull approach. –  moon.watcher Sep 5 '11 at 23:27
    
Nice job, Rolando! +1 –  randy melder Sep 6 '11 at 3:49
    
I guess field() and find_in_set() are nearly identical. I've used the latter in the past. –  Nick Chammas Sep 6 '11 at 17:42
add comment

Typically (it's asked daily in SO) you'd use a CASE which is standard SQL

ORDER BY
    CASE group
          WHEN 9 THEN 1
          WHEN 7 THEN 2
          WHEN 6 THEN 3
          WHEN 10 THEN 4
          WHEN 8 THEN 5
          WHEN 5 THEN 6
          ELSE 7
    END

I'd be interested to see how this compares over a large dataset to the LOCATE(CONCAT...) method from RolandoMySQLDBA

For a more complex example, see 'Conversion failed' error with ORDER BY CASE expression

share|improve this answer
    
Kudos for a simpler approach. String manipulation could be costly. My answer was just quick-and-dirty. Your answer is strictly numerical. +1 !!! –  RolandoMySQLDBA Sep 6 '11 at 16:22
    
You inspired me to come up with another solution. It is quasi-numerical but still functional. –  RolandoMySQLDBA Sep 6 '11 at 16:34
add comment

Use MySQL's find_in_set() function to do this. It is more concise but less portable than the CASE approach gbn proposed.

For example:

SELECT `names`, `group`
FROM my_table
WHERE `group` IN (9,7,6,10,8,5)
ORDER BY find_in_set(`group`,'9,7,6,10,8,5');

Because it relies on string searching, find_in_set() is useful mainly for ordering on small sets of easily searchable keys, like integers.

share|improve this answer
    
I tested this against my sample data. It works. I just added backquotes around group since group is a reserved word for GROUP BY. Otherwise, +1 for your use of find_in_set !!! –  RolandoMySQLDBA Sep 6 '11 at 18:40
    
@Rolando - Updated my answer to add those back ticks around group. –  Nick Chammas Sep 6 '11 at 18:52
add comment

You could create it as an ENUM type.

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, 'a' sorts before 'b' for ENUM('a', 'b'), but 'b' sorts before 'a' for ENUM('b', 'a').

So create the ENUM field as ENUM('9','7','6','10','8','5')

Disclaimer: I do not endorse this, because I suspect that your group column needs to be dynamic. I am just mentioning it as an option.

share|improve this answer
    
hi DTest, I agree, like you say is useful alternative if the values in the column will not change. thankyou! –  moon.watcher Sep 5 '11 at 23:42
    
@DTest - Hey that's slick !!! I never thought about ENUMs in that respect. + 1 !!! –  RolandoMySQLDBA Sep 6 '11 at 0:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.