Как изменить порядок строк в базе данных SQL?

Похоже, вам понадобится добавить еще один столбец, например, "ListOrder". Ваша таблица может выглядеть следующим образом:

BookMark   ListOrder
========   ==========
  d             1
  g             2
  b             3
  f             4
  a             5

Затем вы сможете использовать "order by" для сортировки по ListOrder.

SELECT * FROM MyTable ORDER BY ListOrder

Если пользователь может перемещать закладку только на одну позицию за раз, вы можете использовать целые числа в качестве ListOrder и менять их местами. Например, если пользователь хочет переместить закладку "f" на одну строку вверх:

UPDATE MyTable
SET ListOrder = ListOrder + 1
WHERE ListOrder = (SELECT ListOrder - 1 FROM MyTable WHERE BookMark = 'f');

UPDATE MyTable
SET ListOrder = ListOrder - 1
WHERE BookMark = 'f';

Если пользователь может перемещать закладку вверх или вниз на несколько строк сразу, то вам нужно будет изменить порядок сегмента. Например, если пользователь хочет переместить "f" в самое начало списка, вам нужно сделать следующее:

IF (increment) {
  UPDATE MyTable
  SET ListOrder = ListOrder - 1
  WHERE ListOrder <= 1 -- Новая позиция
      AND ListOrder > (SELECT ListOrder FROM MyTable WHERE BookMark = 'f');
} ELSE {
  UPDATE MyTable
  SET ListOrder = ListOrder + 1
  WHERE ListOrder >= 1 -- Новая позиция
      AND ListOrder < (SELECT ListOrder FROM MyTable WHERE BookMark = 'f');
}

UPDATE MyTable
SET ListOrder = 1 -- Новая позиция
WHERE BookMark = 'f';

Таким образом, вы сможете управлять порядком закладок в таблице.

Как уже упоминали другие, полагаться на физический порядок строк в таблицах базы данных — не самая лучшая идея. Реляционные таблицы скорее концептуально представляют собой неупорядоченные множества, чем упорядоченные списки. Предположение о каком-то определённом физическом порядке может привести к непредсказуемым результатам.

Судя по всему, вам нужно создать отдельный столбец для хранения предпочитаемого порядка сортировки пользователя. Однако вам всё равно потребуется сделать что-то в вашем запросе, чтобы отобразить результаты в этом порядке.

Физический порядок записей в базе данных можно задать с помощью создания кластерного индекса, но это не то, что вы хотели бы делать на произвольной основе, заданной пользователем. Это также может привести к неожиданным результатам.

Чтобы отсортировать результаты вашего запроса, вы можете использовать ORDER BY. Например, чтобы отсортировать пользователей по фамилии, выполните следующий SQL-запрос:

SELECT * FROM User ORDER BY LastName

Это обеспечит, что результаты будут упорядочены по столбцу LastName в алфавитном порядке. Если вам нужно отсортировать в обратном порядке, вы можете добавить DESC после названия столбца:

SELECT * FROM User ORDER BY LastName DESC

Базы данных могут хранить данные любым способом. Использование ключевого слова "ORDER BY" — это единственный способ гарантировать упорядочивание данных. В вашем примере с закладками вы можете использовать поле типа integer, которое будет указывать порядок. Затем вы можете обновлять это поле по мере того, как пользователь перемещает элементы. После этого просто применяйте ORDER BY к этому столбцу, чтобы получить данные в нужном порядке.

Вот решение, которое я использовал несколько раз. Я добавляю дополнительное поле "sort_order" в таблицу и обновляю его при изменении порядка. Это полезно в случаях, когда у меня есть контейнеры с элементами, и порядок этих элементов должен быть редактируемым внутри контейнера. При изменении порядка я обновляю только поле sort_order для элементов в текущем контейнере, что означает, что обновляется не так много строк (чаще всего это всего лишь несколько записей).

Вкратце, я делаю следующее:

1. Добавляю поле sort_order в таблицу элементов.
2. При вставке новой строки устанавливаю sort_order равным id.
3. При изменении порядка (необходимо id элемента, который нужно переместить, и id элемента, после которого нужно вставить):
   • выбираю id и sort_order из элементов, где container = ID, сортируя по sort_order;
   • разбиваю id и sort_order из строк на два массива;
   • удаляю id элемента, который нужно переместить, из списка id;
   • вставляю id элемента, который нужно переместить, после id элемента, после которого нужно вставить;
   • объединяю список id и список sort_order в двумерный массив, в формате [[id, sort_order], [id2, sort_order], ...];
   • выполняю команду update item set sort_order=SORT_ORDER where id=ID (используя executemany) с объединенным списком.

(Если нужно переместить элемент в другой контейнер, после обновления "внешнего ключа контейнера" перемещаю его в начало или конец, в зависимости от приложения.)

(Если обновление затрагивает большое количество элементов, я не думаю, что это решение будет оптимальным.)

Я создал пример на Python и MySQL, который можно найти на http://wannapy.blogspot.com/2010/11/reorder-rows-in-sql-database.html (скопируйте и попробуйте), вместе с дополнительными объяснениями.