473,574 Members | 2,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
found that, I couldn't find docs about this)

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
found that, I couldn't find docs about this)

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.

Similar topics

4
22934
by: Michael J. Astrauskas | last post by:
Does anyone have a function for manipulating GET variables in a URL? I want to be able to modify some parameters without affecting others. An example of what I'm looking for: Let's say the current url is: www.mine.com/home.php?name=joe&page=7&theme=blue I want to be able to run a function like: $newurl = change_get($_SERVER,"page","6");...
12
5216
by: Kin®sole | last post by:
Hi I'm very new to VB (using VB6) I have two lists one blank and one containing names in the format of surname and then forename.I also have a combo box containing forenames.When I select a forename from my combo box I need to add the corresponding surname into the blank list box.What is the best way to do this? hope this make sense TIA
3
2653
by: da Vinci | last post by:
OK, this is a pretty weird question, but shouldn't be to hard to accomplish. Remember, I am a beginner so please, if I say something stupid, be gentle. :-) I do not know vectors yet, which would probably be a better idea than an array for this, but I want to try using an array for now and then will update it once I learn vectors. I havent...
10
3260
by: Kristian Nybo | last post by:
Hi, I'm writing a simple image file exporter as part of a school project. To implement my image format of choice I need to work with big-endian bytes, where 'byte' of course means '8 bits', not 'sizeof(char)'. It seems that I could use bitset<8> to represent a byte in my code --- if you have a better suggestion, I welcome it --- but that...
2
2100
by: Ido Flatow | last post by:
Hi all, I've been exploring the way I can manipulate WSDL.exe using SchemaImporterExtension in order to create a proxy to my liking. My situation is as follows - I have a web site that has multiple asmx files, some of them share the same data types (the famous "Order" example). What I want is this final result: - A class file that...
17
1679
by: thiago777 | last post by:
What is the best way to manipulate bits in a byte? I got to a situation where I need to organize the bit's order of a byte in a big loop (I mean it), so I say "best" in every aspect, including performance. The less convertion instructions and more cpu native ones, the better. Let me be more specific: I have a shift Integer() array that...
1
1761
by: Arielle | last post by:
Working on a reporting tool to communicate with a tool a lot of our internal customers utilize. A new enhancement requested has been to re-order the log. Each "ticket" has a status log that is stored as a CLOB in the database that on the output report we parse out client side to be in a more "intelligent" format that's far more legible than...
4
2486
by: raknin | last post by:
Hi, I built a form in php that contains some check boxes and drop-down boxes and a Add button. What I want to do is manipulating the check box state (checked and uncheked state) in order to disable button and other elemnets form. My question is how can I manipulate the formm element with out submiting it. The codeis display below. <input...
0
7803
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7720
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8230
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7810
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8096
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6451
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5299
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3749
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.