Sort with one parameter forcing the top of the list

Question

I have a PHP application that displays a list of options for the user. The list is created from a simple query to SQL 2000. What I would like to do is specify a specific parameter at the top of the list, and the remaining parameters will be sorted in alphabetical order.

For example, here are the options sorted alphabetically:

Calgary Edmonton Halifax Montreal Toronto 

I would like the list to be as follows:

 **Montreal** Calgary Edmonton Halifax Toronto 

Is there a way I can do this using a single query? Or am I delaying the execution of the query twice and adding the results?

Answer 1

 SELECT name FROM locations ORDER BY CASE WHEN name = 'Montreal' THEN 0 ELSE 1 END, name 

Answer 2

 SELECT name FROM options ORDER BY name = "Montreal", name; 

Note. This works with MySQL, not SQL 2000, as requested by the OP.

Answer 3

 create table Places ( add Name varchar(30), add Priority bit ) select Name from Places order by Priority desc, Name 

Answer 4

I had a similar problem on the website on which I built a full case report. I wanted reports of cases where the name of the victim is known to be sorted at the top, because they are more convincing. Conversely, I wanted all of John Doe's affairs to be downstairs. Since this is also related to people's names, I also had a problem with the name firstname / lastname. I did not want to divide it into two name fields, because some cases are not people at all.

My decision:

I have a "Name" field that is displayed. I also have a NameSorted field that is used in all queries but never displayed. The I / O user interface will take care of converting "LAST, FIRST" to the sort field automatically.

Finally, to “set” the sort, I simply put the appropriate characters at the beginning of the sort field. Since I want the material to come out at the end, I put "zzz" at the beginning. To sort at the top, you can put "!" at the beginning. Again, your editing user interface can take care of this for you.

Yes, I admit it is a little cheezy, but it works. One of the advantages for me is to make more complex queries with associations in different places to create pages compared to RSS, etc. And I don’t need to remember a complex expression to get the right to sort, it’s always easy to sort by “NameSorted” "

Click my profile to see the resulting website.