PG:: Ошибка: выражения SELECT DISTINCT, ORDER BY должны появиться в списке выбора
ActionView::Template::Error (PG::Error: ERROR: для SELECT DISTINCT, выражения ORDER BY должны появляться в списке выбора
Я создаю веб-сайт событий и пытаюсь отсортировать визуализированные rsvps по времени начала события. Существует много RSVPS, поэтому я группирую их по разным, но в последние несколько дней у меня возникли большие трудности с сортировкой результатов без появления этой ошибки в PG. Я посмотрел на некоторые из предыдущих вопросов по этой теме, и все еще довольно потерян. Как я могу заставить это работать? Спасибо вам большое!
@rsvps = Rsvp.where(:voter_id => current_user.following.collect {|f| f["id"]}, :status => 'going').where("start_time > ? AND start_time < ?", Time.now, Time.now + 1.month).order("count_all desc").count(:group => :event_id).collect { |f| f[0] }
<%= render :partial => 'rsvps/rsvp', :collection => Rsvp.where(:event_id => @rsvps).select("DISTINCT(event_id)").order('start_time asc') %>
6 ответов
Предложение ORDER BY может быть применено только после применения DISTINCT. Поскольку для операций DISTINCT учитываются только поля в операторе SELECT, это единственные поля, которые можно использовать в ORDER BY.
Логически, если вы просто хотите получить отдельный список значений event_id, порядок их появления не имеет значения. Если порядок имеет значение, то вы должны добавить start_time в список SELECT, чтобы был контекст для заказа.
Кроме того, эти два предложения SELECT НЕ эквивалентны, поэтому будьте осторожны:
SELECT DISTINCT(event_id, start_time) FROM ...
SELECT DISTINCT event_id, start_time FROM ...
Вторая форма, которую вы хотите. Первый вернет последовательность записей с данными, представленными в виде конструкции ROW (один столбец с кортежем внутри). Второй вернет нормальные столбцы вывода данных. Он работает только так, как ожидалось, в случае с одним столбцом, где конструкция ROW уменьшается, поскольку это только один столбец.
Я знаю, что это довольно старый вопрос, но я просто рассмотрел небольшой пример в своей голове, который помог мне понять, почему у Postgres есть это, казалось бы, странное ограничение на столбцы SELECT DISTINCT / ORDER BY.
Представьте, что в вашей таблице Rsvp есть следующие данные:
event_id | start_time
----------+------------------------
0 | Mar 17, 2013 12:00:00
1 | Jan 1, 1970 00:00:00
1 | Aug 21, 2013 16:30:00
2 | Jun 9, 2012 08:45:00
Теперь вы хотите получить список различных event_ids, упорядоченных по их соответствующим start_times. Но где следует 1 идти? Должен ли он быть первым, потому что один кортеж начинается 1 января 1970 года, или он должен длиться последним из-за 21 августа 2013 года?
Поскольку система баз данных не может принять это решение за вас, а синтаксис запроса не может зависеть от фактических данных, с которыми он может работать (при условии, что event_id является уникальным), мы ограничены для заказа только по столбцам из SELECT пункт.
Что касается фактического вопроса - альтернатива ответу Мэтью использует агрегатную функцию, такую как MIN или же MAX для сортировки:
SELECT event_id
FROM Rsvp
GROUP BY event_id
ORDER BY MIN(start_time)
Явная группировка и агрегация по start_time разрешить базе данных придумать однозначный порядок следования кортежей. Обратите внимание, что читаемость, безусловно, является проблемой в этом случае;)
Синтаксический и логический порядок операций
Я думаю, что путаница в (или также
GROUP BY, если на то пошло), действительно можно понять, только если понять . Он отличается от синтаксического порядка операций, который является основным источником путаницы.
В этом примере он выглядит как связанный с, учитывая его синтаксическую близость, но на самом деле это оператор, который применяется после (проекции). Из-за характера того, что происходит (удаление повторяющихся строк), все непроектируемое содержимое строки больше не доступно после операции, которая включает предложение. По логический порядок операций в SQLлогическому порядку операций (упрощенно) :
- (производит все возможные ссылки на столбцы)
-
WHERE(можно использовать все ссылки на столбцы из) - (можно использовать все ссылки на столбцы из
FROM, и создать новые выражения, и присвоить им псевдоним) - (работает с кортежем, спроектированным)
- (в зависимости от наличия может работать с кортежем, спроектированным
SELECT, а если отсутствует * возможно (в зависимости от диалекта) и в других выражениях)
Что насчет и
Тот факт, что без него можно получить доступ (на некоторых диалектах) также к вещам, которые не были спроецированы, может быть немного странным, но определенно полезным. Например, это работает:
WITH emp (id, fname, name) AS (
VALUES (1, 'A', 'A'),
(2, 'C', 'A'),
(3, 'B', 'B')
)
SELECT id
FROM emp
ORDER BY fname DESC
dbfiddle здесь . Производство
id
--
2
3
1
Это меняется, когда вы добавляете
DISTINCT. Это больше не работает:
WITH emp (id, fname, name) AS (
VALUES (1, 'A', 'A'),
(2, 'C', 'A'),
(3, 'B', 'B')
)
SELECT DISTINCT name
FROM emp
ORDER BY fname DESC
dbfiddle здесь . Ошибка:
ОШИБКА: для SELECT DISTINCT в списке выбора должны появиться выражения ORDER BY. СТРОКА 8:ORDER BY fname DESC.
Потому что что
fname значение, которое вы бы приписали
name = A? или
C? От ответа будет зависеть, получите ли вы в результате или
B,
A. Это не может быть решено.
PostgreSQL
Теперь, как упоминалось в указанной отношениях между ивыше статье , PostgreSQL поддерживает исключение из этого правила, которое иногда может быть полезно:
DISTINCT ON(см. также подобные вопросы ):
WITH emp (id, fname, name) AS (
VALUES (1, 'A', 'A'),
(2, 'C', 'A'),
(3, 'B', 'B')
)
SELECT DISTINCT ON (name) id, fname, name
FROM emp
ORDER BY name, fname, id
dbfiddle здесь , производя:
id |fname|name
---|-----|----
1 |A |A
3 |B |B
Этот запрос позволяет производить только различные значения
name, а затем для каждой повторяющейся строки возьмите первую с учетом
ORDER BYпункт, который делает выбор для каждой отдельной группы однозначным. Это можно эмулировать в других СУБД с помощью оконных функций .
Поскольку вы используете столбец start_time, вы можете использовать row_number(), которая является одной из оконных функций PostgreSQL, и складывать ее в
порядок start_time, если вы ожидаете значения строки с первым start_time
Выберите event_id из (SELECT event_id,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY start_time) AS first_row FROM Rsvp), где first_row = 1
обратный порядок start_time, если вы ожидаете значения строки с последним start_time
Выберите event_id из (SELECT event_id,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY start_time desc) AS last_row FROM Rsvp), где last_row = 1
Вы также можете использовать другую оконную функцию в соответствии с вашими требованиями.
этот ответ GitHub дал мне понимание, которого я не смог найти в других ответах, опубликованных здесь.
В итоге вам нужна та же логика, что и у оператора ORDER BY в операторе SELECT.
В приведенном примере они используют оператор CASE для создания индивидуального заказа.
Однако в примере оператор CASE используется дважды, и в этом нет необходимости, SQL можно упростить до:
SELECT DISTINCT pet.name, pet.alive, CASE WHEN pet.alive THEN 1 ELSE 0
END AS custom_order
FROM (values ('a', true), ('b', true), ('c', false)) AS pet(name, alive)
ORDER BY custom_order;
Предполагая, что это модель в Rails, пример можно записать так:
Pet.select(:name, :alive, Arel.sql("CASE WHEN alive THEN 1 ELSE 0
END AS custom_order")).order(:custom_order)
Если вам не нужно собственное имя в порядке сортировки, ActiveRecord назовет его регистром, который также можно использовать для сортировки.
Pet.select(:name, :alive, Arel.sql("CASE WHEN alive THEN 1 ELSE 0
END")).order(:case)
Я смог решить эту ошибку, добавив столбец в свой выбор, а затем используя этот столбец. я имел
SELECT DISTINCT concat(dl.FirstName, concat(' ', dl.LastName)) as displayName, ...и я хотел фамилию (как делают). Я пробовал каждую перестановку, которую только мог придумать, для добавления
ORDER BY в пределах
SELECT, но сработало просто добавление
, dl.LastName as lastName затем добавив
ORDER BY lastName ASC в конце запроса, как в более типичном запросе.
В итоге у меня появился дополнительный столбец, но его легко игнорировать в моем приложении.