Link to home
Start Free TrialLog in
Avatar of maxmohave
maxmohave

asked on

Can you alias column values to change sort order using SQL?

Question is can you alias a column value to change sort order in SQL?

Background:
We created tables prepending numbers to change fix our search order. We created a column called status in filled in weather status values. Original we wanted "weather watch" to go first. So we prepended a number. Other values including Hurricane, Tornado, snow and clear weather as below:

05 Weather Watch
10 Hurricane Watch
15 Tornado Watch
20 Snow Watch
25 Clear Weather

Then when we sorted by status weather watch would always go first. For example,

select * from mysort order by region,status;
+----+--------------------+------------+-------------+
| id | status                        | region     | city        |
+----+--------------------+------------+-------------+
|  1 | 05 Weather Watch   | East Coast | New York  |
|  7 | 20 Snow Watch       | East Coast | Boston      |
|  8 | 25 Clear Weather     | East Coast | New Jersey  |
|  5 | 05 Weather Watch   | Gulf       | Seattle     |
|  2 | 10 Hurricane Watch | Gulf       | New Orleans |
|  3 | 25 Clear Weather   | Gulf       | Tampa       |
|  9 | 25 Clear Weather   | Gulf       | Houston     |
|  6 | 10 Hurricane Watch  | Midwest    | St Louis    |
|  4 | 15 Tornado Watch   | Midwest    | Kansas City |
+----+--------------------+------------+-------------+

Problem is now is for some sorts we want "05 Weather Watch" to go first,.

But for others we want 10 Hurricane Watch to go first.
And for the other categories, so we don't care what comes:

after "10 Hurricane Watch" it can be "05 Weather watch" or tornado watch, etc. As long as the region
is in order (east coast, gulf, midwest).

I have included my very simple table and query to show what I'm working with. Right now. I believe only solution is to do two sorts.

Is that correct? Thank you, craig
create DATABASE test;
use test;
 
drop table mysort;
 
create table mysort (
	id int unsigned not null auto_increment,
	status varchar(40) not null,
	region varchar(40) not null,
	city varchar(50) not null,
	primary key (id)
);
insert into mysort values (1,"05 Weather Watch","East Coast","New York");
insert into mysort values (2,"10 Hurricane Watch","Gulf","New Orleans");
insert into mysort values (3,"25 Clear Weather","Gulf","Tampa");
insert into mysort values (4,"15 Tornado Watch","Midwest","Kansas City");
insert into mysort values (5,"05 Weather Watch","Gulf","Seattle");
insert into mysort values (6,"10 Hurricane Watch","Midwest","St Louis");
insert into mysort values (7,"20 Snow Watch","East Coast","Boston");
insert into mysort values (8,"25 Clear Weather","East Coast","New Jersey");
insert into mysort values (9,"25 Clear Weather","Gulf","Houston");
 
select * from mysort order by region,status;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maxmohave
maxmohave

ASKER

I see what exactly what you are saying but as far as implementing this using another column. Can you elaborate. Say I added column sort to "mysort' table and added the numbers:

create table mysort (
      id int unsigned not null auto_increment,
   sort varchar(6) not null,
      status varchar(40) not null,
      region varchar(40) not null,
      city varchar(50) not null,
      primary key (id)
);
insert into mysort values (1,"05","Weather Watch","East Coast","New York");
insert into mysort values (2,"10", "Hurricane Watch","Gulf","New Orleans");
insert into mysort values (3,"25", "Clear Weather","Gulf","Tampa");
etc.

If I wanted to make Hurricane Watch go before say Weather Watch, I would update my table
with command
update mysort set sort="10" where status="Weather Watch";
update mysort set sort="05" where status="Hurricane Watch";

Is this what you are getting at?

thanks,
Please note, I asked for additional clarification. But question was answered.
>If I wanted to make Hurricane Watch go before say Weather Watch, I would update my table
with command
update mysort set sort="10" where status="Weather Watch";
update mysort set sort="05" where status="Hurricane Watch";

Yes
But if you want to sort it sometimes one way and sometimes another, then you need two sorting columns.