# Indirect Arbitrary Sorting in SQL

Generally, SQL defines 3 sort orders:

- numeric
- date
- string

Strings tend to be a catch-all data type. While Relational Theory would prefer data to come from pre-defined “domains” — sets of acceptable data — SQL databases treat most of these types as strings, and leave it up to various constraints to limit the scope of valid values.

One down side of treating all of these data types as strings is how they are sorted: alphabetically. Very often, alpabetical order is _not_ the most suitable.

For example, suppose we have columns to store the following data:

- day (name) of the week
- month (name)
- rainbow colour

SQL has no direct way of sorting on this column using any other than alphabetical order.

## An Example: Rainbow Colour

You probably learned at a young age that the colours of the rainbow are:

- Red
- Orange
- Yellow
- Green
- Blue
- Indigo
- Violet

The order is not truly arbitrary: they are the order in which they appear in the rainbow, due to their increasing wave frequency.

Certainly, the list is not alphabetical.

For our example, we will imagine that we have colour-coded our data in the above order.

### Creating the Colour Table

First we create a table to store the colours:

```sql
CREATE TABLE colours(
	id INT PRIMARY KEY,
	name INT,
	sort INT
);
```

Note the name of the last column. I would have loved to call it `order`, but that’s a reserved word. We could have forced the issue by using `"order"` but that’s a _very_ bad idea.

The `sort` column will allow us to change the actual sort order if necessary. For example, if we were to add another column between two others, it would have to go at the end of the table, but its `sort` value could reflect its true order.

We now populate the table with data:

```sql
INSERT INTO colours(id,colour,sort)
VALUES
	(1,'red',1),(2,'orange'2,),(3,'yellow',3),
	(4,'green,4),(5,'blue',5),(6,'indigo',6),(7,'violet',7);
```

At this point, the `sort` column has the same values as the `id` column, which makes it look redundant. It’s possible that it will stay this way, but future changes may make a mess of the `id`, while the `sort` will have to be readjusted.

### Adding a Colour to the Main Table

Suppose we have a table of data, such as:

```sql
CREATE TABLE data (
	id INT PRIMARY KEY,
	…,	--	other columns
	colour INT REFERENCES colours(name),
	…	--	other columns
);
```

The foreign key (`REFERENCES`) ensures that we have a __valid__ colour name.

### Sorting

There are two ways we can sort the `data` table by colour.

#### Sorting by Sub Query

We can use a correlated sub query to extract the `sort` value from the `colours` table:

```sql
SELECT *
FROM data
ORDER BY (SELECT sort FROM colours WHERE colours.name=data.colour);
```

The Sub Query fetches the `sort` value form the `colours` table where the colour names match.

This has the benefit of being simple and to the point.

#### Sorting Using a Join

Alternatively, we could join the two tables, and sort with the joined `sort` value:

```sql
SELECT c.*
FROM data d LEFT JOIN colours c ON d.colour=c.name
ORDER BY c.sort;
```

This has the benefit of not including any nesting, though you may need to qualify columns in your query.

Note that there is probably no performance difference between the two techniques. It is purely a matter of preference.

/*	Sorting by Secondary Table
	================================================
	Long Version
		- Use Colour ID
		- Use Allocated Sort Order
	================================================ */
	CREATE TABLE colours(
		id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
		colour INT,
		sort INT
	);
	insert into colours(colour) VALUES('red',1),('orange',2),('yellow',3),('green',4),('blue',5),('indigo',6),('violet',7);

	alter table customers add colour int;
	update customers set colour=(select id from colours where customers.id=customers.id order by random() limit 1);

	--	Using Subquery
		SELECT * FROM customers
		ORDER BY (SELECT sort FROM colours WHERE colours.id=customers.colour);
	
	--	Using JOIN
		SELECT c.*
		FROM customers c LEFT JOIN colours k ON c.colour=k.id
		ORDER BY k.sort;