0

What should I use in the outer GROUP BY, or what is the error?

Without using MIN() in both outer and inner queries, it's working fine.

SELECT DISTINCT (C.id) AS `course_id`, C.name, C.type, C.description, C.image, C.extra,
    C.first_flow_question_id, C.menu_flow_question_id, C.last_question_id, C.lang_id, C.author_id,
    C.organization_id, C.country_id, C.created_at, C.updated_at, C.deleted_at, M.id AS `message_id`,
    MIN(M.date_created) AS `first_message`, MAX(M.date_created) AS `last_message`
FROM `user` U, `course` C, `user_course_group` UCG, `message` M
WHERE U.id = 244
AND U.id = UCG.user_id
AND M.course_id = C.id
AND C.id = UCG.course_id
AND C.deleted_at IS NULL
AND C.last_question_id IS NOT NULL
AND UCG.last_question_id IS NOT NULL
AND UCG.last_question_id = C.last_question_id
AND (M.course_id, MIN(M.date_created), MAX(M.date_created)) IN 
(
    SELECT C.id, MIN(M.date_created), MAX(M.date_created)
    FROM message M, course C
    WHERE (M.`from_user_id`= 244 OR M.`to_user_id`= 244)
    AND M.course_id = C.id
    GROUP BY C.id
)
GROUP BY C.id;
underscore_d
  • 6,309
  • 3
  • 38
  • 64
Ahmad
  • 1
  • 5
  • 2
    A group by should be by all non-aggregated columns in the select. It's not clear what you are trying to do by reverse engineering the code. Please explain and add sample data and expected output as text to the question. BTW distinct with group by is just wrong and is over the entire row not just the column in brackets. – P.Salmon Jan 11 '21 at 13:02
  • @P.Salmon I'm trying to get the first and last message sent by a specific user for all courses. I have used the other non-aggregated columns but the same issue – Ahmad Jan 11 '21 at 13:12
  • Ouch, this query has lots of problems, not just the one indicated by the error message. The one indicated by the error message is caused by M.id (or in earlier mysql versions everything except c.id and the aggregated fields), since mysql does not know what values it should assign to the message id: min and max would have different message id associated with it. Answers to the following SO question can get you started to sort out this mess: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – Shadow Jan 11 '21 at 13:16
  • I have 4 tables: User: id, username, phone_number, ... course: id, name, ... user_course_group: user_id, course_id, ... message: id, date_created, course_id, from(which is the phone number of the user) so user_course_group will connect User and course tables and table message will be linked to table user via phone_number and to table course via course_id – Ahmad Jan 11 '21 at 13:19
  • Unrelated to your problem, but: `distinct` is **not** a function. It always applies to all columns in the select list. Enclosing one of the columns with parentheses won't change anything and is useless. `distinct (a),b` is the same as `distinct a,(b)` or `distinct a,b` –  Jan 14 '21 at 14:55
  • [Edit] your question to include table schemas, sample input data, desired output data, and all error(s) or wrong output(s) received as text in the question itself. – underscore_d Jan 14 '21 at 14:56

0 Answers0