MySQL: Using DISTINCT and GROUP BY together

aggregatedistinctgroup byMySQLoracle

I saw the following MySQL query that that uses both DISTINCT and GROUP BY together:

SELECT  DISTINCT user_id, post_id, post_content
    FROM  some_table
    GROUP BY  post_id, user_id
    HAVING  post_content LIKE '%abc%';

Here is a scenario to go along with the query: Each user has a unique id, user_id, and can make multiple posts which are identified by a unique id, post_id. Each post would contain some text.

I found this confusing(after coming from Oracle DBs) and had below questions:

  1. What is the meaning of using GROUP BY without doing any aggregation?
  2. What is the significance of switching the order of columns in SELECT vs in GROUP BY?
  3. What is the meaning of omitting the third column from GROUP BY?
  4. Why is DISTINCT used along with GROUP BY? Does distinct operation run after all the groupings are done on the final result or before?

Best Answer

ad 1) Old mysql databases and when you disable ONLY_FULL_GROUP_BY , you can make this query and if the post_content are all equal you would notice, that mysql delivers a random not deterministic value back.

ad 2) none what so ever

ad 3) lazy programming and it occurs an error when you enable ONLY_FULL_GROUP_BY

ad 4) No, it would display all post_content that are connected to user_id, post_id similar to addind post_content to the Group by

Like Strawberry already said this query doesn't make any sense

Related Question