Получить значение ячейки, зная, что у другой ячейки в этой строке - максимальное значение

@maxkoshevoi · Регистрация: 07.11.2013

Студворк — интернет-сервис помощи студентам

Да, может заголовок не очень, но я не знаю как лучше вкратце описать проблему.
В общем, у меня есть результаты тестирования, которые состоят из даты и собственно результата, задача: получить для каждого пользователя 5 последних результатов и отсортировать это всё по последнему результату.
С первым пунктом я справился:

SQL

SELECT 
   CONCAT(u.user_lname, ' ', u.user_fname) AS 'Пользователь', 
   SUBSTRING_INDEX(GROUP_CONCAT(ROUND(r.bal) ORDER BY r.date DESC SEPARATOR ', '), ', ', 5) AS 'Последние результаты' 
FROM reports r JOIN users u ON r.user_id = u.id 
WHERE r.test_id = 6 
GROUP BY r.user_id;

А вот со вторым пунктом проблемы.. Я сначала думал отсортировать так:

SQL

1	ORDER BY SUM(IF(r.date=MAX(r.date), r.bal, 0));

Но, увы, нельзя использовать MAX внутри SUM. А так бы всё сработало, ну что за несправедливость =)

Так что я уже не знаю, как реализовать сортировку не находя ещё одним запросом нужный балл, а это не вариант, так как придётся туда так же прописывать WHERE и следовательно все JOIN'ы

@retvizan · 20.11.2016, 02:50

в части select добавьте ещё определение последнего результата и по нему сортируйте

@maxkoshevoi · 20.11.2016, 13:32 **[ТС]**

retvizan, Если добавить это

SQL

1	SUM(IF(r.DATE=MAX(r.DATE), r.bal, 0))

в SELECT, запрос всё так же не будет выполнятся (с той же ошибкой: Invalid use of group function).

Я даже пробовал в SELECT добавить это

SQL

1	MAX(r.DATE) AS 'maxDate'

И потом в ORDER BY это

SQL

1	ORDER BY SUM(IF(r.DATE=maxDate, r.bal, 0));

Но, хотя в самом ORDER BY можно использовать алиасы, внутри SUM функцию MAX всё так же использовать нельзя (даже по псевдониму), ошибка: Reference 'maxDate' not supported (reference to group function)

@retvizan · 20.11.2016, 14:23

maxkoshevoi,

MySQL

SELECT 
   CONCAT(u.user_lname, ' ', u.user_fname) AS 'Пользователь', 
   SUBSTRING_INDEX(GROUP_CONCAT(ROUND(r.bal) ORDER BY r.DATE DESC SEPARATOR ', '), ', ', 5) AS 'Последние результаты',
SUBSTRING_INDEX(GROUP_CONCAT(r.bal ORDER BY r.DATE DESC SEPARATOR ', '), ', ', 1) AS 'Последний результат' 
FROM reports r JOIN users u ON r.user_id = u.id 
WHERE r.test_id = 6 
GROUP BY r.user_id
order by 3;

если не то, то приведите тестовый пример данных и какой должен быть результат.

@maxkoshevoi · 20.11.2016, 18:16 **[ТС]**

retvizan, это гениально!!!
Только один маленький недочёт. Оно сортирует как строку, но это легко исправить добавив CAST(... AS DECIMAL)
Итоговый запрос:

SQL

SELECT
   CONCAT(u.user_lname, ' ', u.user_fname) AS 'Пользователь',
   SUBSTRING_INDEX(GROUP_CONCAT(ROUND(r.bal) ORDER BY r.DATE DESC SEPARATOR ', '), ', ', 5) AS 'Последние результаты'
FROM reports r JOIN users u ON r.user_id = u.id
WHERE r.test_id = 6 
GROUP BY r.user_id
ORDER BY CAST(SUBSTRING_INDEX(GROUP_CONCAT(r.bal ORDER BY r.DATE DESC SEPARATOR ','), ',', 1) AS DECIMAL) DESC;

Правду говорят, всё гениальное - просто, огромное спасибо =)

Добавлено через 7 минут
ПС: Запрос, который я до этого написал через self join, ищет только последний результат (а не, например, 5 последних) и работает в 2 раза дольше

Добавлено через 3 часа 35 минут
Заметил ещё одну ошибку. Если последний результат - null (такой тоже может быть), но GROUP_CONCAT его проигнорирует и получается для этого пользователя в сортировке будет учитываться уже предпоследний результат. Так что я ещё немного подправил ORDER BY, чтобы в любом случае сортировка шла по последнему результату:

SQL

1	ORDER BY CAST(NULLIF(SUBSTRING_INDEX(GROUP_CONCAT(IFNULL(r.bal, '-') ORDER BY r.DATE DESC SEPARATOR ','), ',', 1), '-') AS DECIMAL) DESC;

@retvizan 411 / 365 / 142 Регистрация: 09.04.2011 Сообщений: 1,050
	20.11.2016, 02:50	2
	в части select добавьте ещё определение последнего результата и по нему сортируйте 0

	20.11.2016, 18:16

Опции темы

Получить значение ячейки, зная, что у другой ячейки в этой строке - максимальное значение

Решение