CODEWITHSUNDEEP
sqlsorting
Wally Lawless
Wally Lawless

Reputation: 7557

Sort with one option forced to top of list

I have a PHP application that displays a list of options to a user. The list is generated from a simple query against SQL 2000. What I would like to do is have a specific option at the top of the list, and then have the remaining options sorted alphabetically.

For example, here's the options if sorted alphabetically: 

Calgary  
Edmonton  
Halifax  
Montreal  
Toronto

What I would like the list to be is more like this: 

**Montreal**  
Calgary  
Edmonton  
Halifax  
Toronto

Is there a way that I can do this using a single query? Or am I stuck running the query twice and appending the results?

Upvotes: 4

Views: 2002

Answers (5)

Deepstop
Deepstop

Reputation: 3817

I ended up with this

SELECT name
FROM locations
LEFT JOIN (VALUES ('Toronto', 1), ('Montreal', 2)) city (name, rank)
    ON locations.name = city.name
ORDER BY city.rank, locations.name;

Which may be overkill for this example but can be extended for more complex needs.

Upvotes: 0

Matt Rogish
Matt Rogish

Reputation: 24873

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

Upvotes: 6

mercutio
mercutio

Reputation: 22587

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

Note: This works with MySQL, not SQL 2000 like the OP requested.

Upvotes: 4

Tim Farley
Tim Farley

Reputation: 11950

I had a similar problem on a website I built full of case reports. I wanted the case reports where the victim name is known to sort to the top, because they are more compelling. Conversely I wanted all the John Doe cases to be at the bottom. Since this also involved people's names, I had the firstname/lastname sorting problem as well. I didn't want to split it into two name fields because some cases aren't people at all.

My solution:

I have a "Name" field which is what is displayed. I also have a "NameSorted" field that is used in all queries but is never displayed. My input UI takes care of converting "LAST, FIRST" entered into the sorting field into the display version automatically.

Finally, to "rig" the sorting I simply put appropriate characters at the beginning of the sort field. Since I want stuff to come out at the end, I put "zzz" at the beginning. To sort at the top you could put "!" at the beginning. Again your editing UI can take care of this for you.

Yes, I admit its a bit cheezy, but it works. One advantage for me is I have to do more complex queries with joins in different places to generate pages versus RSS etc, and I don't have to keep remembering a complex expression to get the sorting right, its always just sort by the "NameSorted" field.

Click my profile to see the resulting website.

Upvotes: 0

Garry Shutler
Garry Shutler

Reputation: 32698

create table Places (
    add Name varchar(30),
    add Priority bit
)

select   Name
from     Places
order by Priority desc,
         Name

Upvotes: 0

Related Questions