Mysql, complex ORDER BY

Two columns town and priority.

I need to sort table, so that towns with priority=1 would be first and not sorted by name ASC, while the 开发者_运维百科rest gets sorted by name ASC.

How would i do that?

Thanks ;)

Update

SELECT *
FROM map_towns
ORDER BY priority DESC, town

Like this, but so that priority were from 1 to 12+ instead of 12 to 1.

Like that:

town priority
b_town1 1
a_town2 2
d_town3 3
c_town4 4
a_town5 NULL
b_town6 NULL
c_town7 NULL
d_town8 NULL

etc...

By default, MySQL sorts nulls first

I created a small test case (rows inserted non-sorted on purpose).

create table map_towns(
   town varchar(30) not null
  ,priority int null
);

insert into map_towns(town, priority) values('d_town3', 3);
insert into map_towns(town, priority) values('a_town2', 2);
insert into map_towns(town, priority) values('c_town4', 4);
insert into map_towns(town, priority) values('b_town1', 1);
insert into map_towns(town, priority) values('b_town6', NULL);
insert into map_towns(town, priority) values('d_town8', NULL);
insert into map_towns(town, priority) values('a_town5', NULL);
insert into map_towns(town, priority) values('c_town7', NULL);

The following query should do what you ask for.

select town
      ,priority
      ,isnull(priority) 
 from map_towns 
order by isnull(priority), priority, town;

+---------+----------+------------------+
| town    | priority | isnull(priority) |
+---------+----------+------------------+
| b_town1 |        1 |                0 |
| a_town2 |        2 |                0 |
| d_town3 |        3 |                0 |
| c_town4 |        4 |                0 |
| a_town5 |     NULL |                1 |
| b_town6 |     NULL |                1 |
| c_town7 |     NULL |                1 |
| d_town8 |     NULL |                1 |
+---------+----------+------------------+

Here is a link on ISNULL documentation

My idea:

SELECT * FROM Towns
ORDER BY IF(priority = 1, 0, 1) ASC,
         town ASC;

Well just simply make it so that the Priority by default is 0, and then each Town you have you can sort them based on a number. I would normally do something like DisplayOrder which in terms could be your Priority.

something like this.

SELECT * FROM Towns
ORDER BY priority ASC,
name ASC;

So if you have something like

id, name, priority
-----------------------
1, Smithtown, 0
2, Rocktown, 2
3, Georgetown, 1
4, Rockton, 2

The ordering then would be

1, Smithtown, 0
3, Georgetown, 1
4, Rockton, 2
2, Rocktown, 2

SELECT  *
FROM    map_towns
ORDER BY
        priority IS NULL, priority, town