473,574 Members | 2,213 Online

# manipulating ORDER BY

Hello,

I have a table, say

----------
ID|myvalue
----------
1|B
2|C
3|A
4|A
5|B
6|C
----------

Is it possible to perform a SELECT statement so that the output gets
ordered by column "myvalue" by B, then A, then C ?

Someting like:
SELECT * FROM mytable ORDER BY myvalue('B','A' ,'C');

This is only an example of course but I wonder if this principle were
possible.

thanks,
Bart
Jul 20 '05 #1
10 1738
Bart Van der Donck wrote:
Hello,

I have a table, say

----------
ID|myvalue
----------
1|B
2|C
3|A
4|A
5|B
6|C
----------

Is it possible to perform a SELECT statement so that the output gets
ordered by column "myvalue" by B, then A, then C ?

Someting like:
SELECT * FROM mytable ORDER BY myvalue('B','A' ,'C');

One way is to create another table, where you tell in what order values
should be:

table myvalue_order
----------------------
order_number | value
1 | B
2 | A
3 | C
----------------------

And then use a join, like this:

select mytable.* from mytable, value_order
where mytable.myvalue =value_order.va lue
order by myvalue_order.o rder_number;

Note that if a query like this is used, then rows where value with
order_number is used, are not displayed at all. If you want to get those
values also, you could use:

select mytable.* from mytable
left outer join value_order on mytable.myvalue =value_order.va lue
order by myvalue_order.o rder_number;

But this would place rows without order number at the first place. You
could of course use opposite order numbers

table myvalue_order
----------------------
order_number | value
3 | B
2 | A
1 | C
----------------------

And then do ... order by order_number desc;

And null values should be at the bottom of the list.
Jul 20 '05 #2
Bart Van der Donck wrote:
Hello,

I have a table, say

----------
ID|myvalue
----------
1|B
2|C
3|A
4|A
5|B
6|C
----------

Is it possible to perform a SELECT statement so that the output gets
ordered by column "myvalue" by B, then A, then C ?

Someting like:
SELECT * FROM mytable ORDER BY myvalue('B','A' ,'C');

One way is to create another table, where you tell in what order values
should be:

table myvalue_order
----------------------
order_number | value
1 | B
2 | A
3 | C
----------------------

And then use a join, like this:

select mytable.* from mytable, value_order
where mytable.myvalue =value_order.va lue
order by myvalue_order.o rder_number;

Note that if a query like this is used, then rows where value with
order_number is used, are not displayed at all. If you want to get those
values also, you could use:

select mytable.* from mytable
left outer join value_order on mytable.myvalue =value_order.va lue
order by myvalue_order.o rder_number;

But this would place rows without order number at the first place. You
could of course use opposite order numbers

table myvalue_order
----------------------
order_number | value
3 | B
2 | A
1 | C
----------------------

And then do ... order by order_number desc;

And null values should be at the bottom of the list.
Jul 20 '05 #3
"Bart Van der Donck" <ba**@nijlen.co m> wrote in message
ID|myvalue
----------
1|B
2|C
3|A
4|A
5|B
6|C
----------

Is it possible to perform a SELECT statement so that the output gets
ordered by column "myvalue" by B, then A, then C ?

Does

SELECT * FROM mytable ORDER BY if(myvalue='A', 2, ...);

work?
Jul 20 '05 #4
"Bart Van der Donck" <ba**@nijlen.co m> wrote in message
ID|myvalue
----------
1|B
2|C
3|A
4|A
5|B
6|C
----------

Is it possible to perform a SELECT statement so that the output gets
ordered by column "myvalue" by B, then A, then C ?

Does

SELECT * FROM mytable ORDER BY if(myvalue='A', 2, ...);

work?
Jul 20 '05 #5
Siemel Naran wrote:
Does

SELECT * FROM mytable ORDER BY if(myvalue='A', 2, ...);

work?

I'm not sure how did you plan to use that, but at least this should work
(tested it):

select * from mytable
order by case myvalue
when 'A' then 2
when 'B' then 1
when 'C' then 3
end;
Jul 20 '05 #6
Siemel Naran wrote:
Does

SELECT * FROM mytable ORDER BY if(myvalue='A', 2, ...);

work?

I'm not sure how did you plan to use that, but at least this should work
(tested it):

select * from mytable
order by case myvalue
when 'A' then 2
when 'B' then 1
when 'C' then 3
end;
Jul 20 '05 #7
Aggro wrote:
I'm not sure how did you plan to use that, but at least this should work
(tested it):

select * from mytable
order by case myvalue
when 'A' then 2
when 'B' then 1
when 'C' then 3
end;

Thanks both - this little jewel does the trick indeed (wonder how you

This works with numeric fields as well, eg:

select * from mytable
order by case ID
when '6' then 1
when '4' then 2
when '5' then 3
when '3' then 4
when '1' then 5
when '2' then 6
end;

gives output:

6|C
4|A
5|B
1|B
3|A
2|C

My problem occurs in this query:

select * from mytable
order by case ID
when '6' then 1
when '4' then 2
when '5' then 3
end;

gives output:

1|B
2|C
3|A
6|C
4|A
5|B

This is not what I want. It should be:

6|C
4|A
5|B
1|B
2|C
3|A

The closest idea I had, was something like this:

select * from mytable
order by case ID
when NOT NULL then 4
when '6' then 1
when '4' then 2
when '5' then 3
end;

But that doesn't change anything in my result set (however it returns
no error).

These other ideas die with an error:
when IS NOT NULL then 4
when REGEXP '' then 4
when != NULL

I use MySQL 4.0.
Any ideas?

Bart
Jul 20 '05 #8
Aggro wrote:
I'm not sure how did you plan to use that, but at least this should work
(tested it):

select * from mytable
order by case myvalue
when 'A' then 2
when 'B' then 1
when 'C' then 3
end;

Thanks both - this little jewel does the trick indeed (wonder how you

This works with numeric fields as well, eg:

select * from mytable
order by case ID
when '6' then 1
when '4' then 2
when '5' then 3
when '3' then 4
when '1' then 5
when '2' then 6
end;

gives output:

6|C
4|A
5|B
1|B
3|A
2|C

My problem occurs in this query:

select * from mytable
order by case ID
when '6' then 1
when '4' then 2
when '5' then 3
end;

gives output:

1|B
2|C
3|A
6|C
4|A
5|B

This is not what I want. It should be:

6|C
4|A
5|B
1|B
2|C
3|A

The closest idea I had, was something like this:

select * from mytable
order by case ID
when NOT NULL then 4
when '6' then 1
when '4' then 2
when '5' then 3
end;

But that doesn't change anything in my result set (however it returns
no error).

These other ideas die with an error:
when IS NOT NULL then 4
when REGEXP '' then 4
when != NULL

I use MySQL 4.0.
Any ideas?

Bart
Jul 20 '05 #9
Bart Van der Donck wrote:
This is not what I want. It should be:

6|C
4|A
5|B
1|B
2|C
3|A

Does it matter in what order 1,2 and 3 are? If not, then just

select * from mytable
order by case ID
when '6' then 1
when '4' then 2
when '5' then 3
else 4
end;

Case statement is explained in:
http://dev.mysql.com/doc/mysql/en/CASE_Statement.html
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.