Сортировка с выбором порядка значений

Постановка задачи

Есть следующий набор данных:

Требуется отсортировать записи по полю type в некотором заданном порядке. Например, в таком: 'Село', 'Деревня', 'Поселок', 'Город', 'Район', 'Область'.

Подготовка

DROP TABLE IF EXISTS `table`;

CREATE TABLE `table` (
	id INT,
	type VARCHAR(255)
);

INSERT INTO `table` VALUES
(1, 'Город'),
(2, 'Село'),
(3, 'Деревня'),
(4, 'Область'),
(5, 'Поселок'),
(6, 'Район'),
(7, 'Станица'),
(8, 'Город'),
(9, 'Область'),
(10, 'Район');

Решение

Для решения данной задачи воспользуемся функцией FIELD(field, val_1, val_2, ...), где:

• field — название поля, по которому будем производить сортировку;
• val_1, val_2, ... — порядок сортировки значений поля field.

Функция FIELD() работает следующим образом. Значение переданное в первом аргументе (field) последовательно сравнивается со значениями последующих аргументов (val_1, val_2, ...) и в случае их совпадения возвращается порядковый номер этого аргумента. Т.е. при совпадении с val_1 будет возвращена 1, при совпадении с val_2 — 2 и т.д. Если совпадений не найдено либо значение поля равно NULL, то будет возвращен 0.

SELECT *
FROM `table`
ORDER BY
	FIELD(type, 'Село', 'Деревня', 'Поселок', 'Город', 'Район', 'Область') ASC,
	id ASC;

Результат запроса:

Комментарии:

1. Порядковые номера, возвращаемые функцией FIELD(), можно дополнительно отсортировать по возрастанию (ASC) или убыванию (DESC).
2. Выражение id ASC было добавлено для уточнения порядка сортировки записей с одинаковыми значениями в поле type.

Сортировка несовпадающих значений

Т.к. в 7-й записи в поле type указана 'Станица', а данное значение не было передано в функцию FIELD(), то в этом случае был возвращен 0. И соответственно 7-я запись при сортировке оказалась на первом месте.

Как вывести несовпадающие значения последними? Для этого нужно передать в функцию требуемый список значений в обратном порядке, а результаты работы функции отсортировать по убыванию, т.е. FIELD(type, 'Область', 'Район', 'Город', 'Поселок', 'Деревня', 'Село') DESC. Таким образом, 7-я запись окажется последней, а требуемый порядок сохранится.

SELECT *
FROM `table`
ORDER BY
	FIELD(type, 'Область', 'Район', 'Город', 'Поселок', 'Деревня', 'Село') DESC,
	id ASC;

Результат запроса: