0

Suppose I have a column with colour names (colour). Naturally, it will be a character field.

I would like to sort the table by colour, but not in alphabetical order, but by rainbow or transistor colour code order.

What is the simplest way to sort a column using an arbitrary sort order? Something like this:

id  name        colour
1   this        red
4   that        red
2   stuff       orange
5   nonsense    orange
3   whatever    yellow
6   etc         yellow

where the colours in in rainbow order.

The use of colours is just an example. There are many sets of textual data where the order is not alphabetical.

  • Either create an explicit sort order table for each attribute that you want to order by, or use an implicit order relation such as: select ... from ... order by case when color = 'red' then 1 when color = 'orange' then 2 when ... end. Not sure why you have two id's per color in your example though. – Lennart Mar 28 '17 at 11:06
  • @Lennart It was a simplified example. I have added another column to be more realistic. The point is that I would like to sort non-alphabetically. Surely there must be a simpler solution than using case? – Manngo Mar 28 '17 at 11:12
  • Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Mar 28 '17 at 11:17
  • @a_horse_with_no_name I’m trying to keep this generic. Personally I use Postgres, but I often find myself using MSSQL & MariaDB. – Manngo Mar 28 '17 at 11:19
1

I would use another table with the key values (also suggested by @McNets) feel its less verbose and more scalable.

-----------------------------
|         Colours           |
-----------------------------
|  KeyValue  |    Name      |
-----------------------------
|     1      |  Red         |
|     2      |  Orange      |
|     3      |  Yellow      |
-----------------------------

-----------------------------
|        OtherTable         |
-----------------------------
| id |  Name    |  Colour   |
-----------------------------
| 1  | this     |   red     |
| 4  | that     |   red     |
| 2  | stuff    |   orange  |
| 5  | nonsense |   orange  |
| 3  | whatever |   yellow  |
| 6  | etc      |   yellow  |
-----------------------------

SELECT
    ot.Id,
    ot.Name,
    ot.Colour
FROM OtherTable ot
INNER JOIN Colours c on c.Name = ot.Colour
ORDER BY c.KeyValue
  • I’m inclined to agree. That was my first instinct, but I was hoping there was a slicker solution. Thanks. – Manngo Mar 28 '17 at 11:25
2

Use a case statement to establish the order, or add a lookup table with all colours where you can set an order.

declare @t table(id int, name varchar(20), colour varchar(10));
insert into @t values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

select *
from   @t
order by (case when colour = 'yellow' then  1
               when colour = 'orange' then 0
               else 2
           end);
GO
id | name     | colour
-: | :------- | :-----
 2 | stuff    | orange
 5 | nonsense | orange
 3 | whatever | yellow
 6 | etc      | yellow
 1 | this     | red   
 4 | that     | red   

dbfiddle here

create table foo(id int, name varchar(20), colour varchar(10));
insert into foo values
(1,   'this',        'red'),
(4,   'that',        'red'),
(2,   'stuff',       'orange'),
(5,   'nonsense',    'orange'),
(3,   'whatever',    'yellow'),
(6,   'etc',         'yellow');

create table bar(colour varchar(10), [order] int);
insert into bar values
('orange', 0),
('yellow', 1),
('red', 2);



select *
from   foo
inner join bar
on foo.colour = bar.colour
order by bar.[order];


GO
id | name     | colour | colour | order
-: | :------- | :----- | :----- | ----:
 2 | stuff    | orange | orange |     0
 5 | nonsense | orange | orange |     0
 3 | whatever | yellow | yellow |     1
 6 | etc      | yellow | yellow |     1
 1 | this     | red    | red    |     2
 4 | that     | red    | red    |     2

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.