MYSQL Group By deep dive, problems and issues only_full_group_by mode, Group by hits the first matching result

https://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql

SELECT * 
FROM (
    SELECT * FROM table_name
    ORDER BY date ASC, time ASC 
) AS sub
GROUP BY name

GROUP BY groups on the first matching result it hits. If that first matching hit happens to be the one you want then everything should work as expected.

https://stackoverflow.com/questions/45484068/what-are-the-benefits-of-only-full-group-by-mode

MYSQL/SQL Group by comes with only_full_group_by mode, meaning, the item you want to group by has to be in select.

only_full_group_by = on tells MySQL engine: Do not apply GROUP BY when you have doubt about what results to show and throw an error. only apply it if the command specifically tells you what to do. i.e. when the command is full and complete!

only_full_group_by = off tells MySQL engine: always apply GROUP BY and if you have doubt about what results to choose, just pick one randomly!

You will not turn it off if you use GROUP BY properly!

Example:

Table: users

 id   |  name
----------------
  1      ali
  2      john
  3      ali

When you use GROUP BY on the name column:

SELECT * FROM users GROUP BY name;

There are two possible results:

  1      ali
  2      john     

OR

  2      john
  3      ali

MYSQL does not know what result to choose! Because there are different ids but both have name=ali.

Solution1:

only selecting the name field:

SELECT name FROM users GROUP BY name;

result:

  ali
  john     

This is a perfect solution. removing columns that makes GROUP BY confused. This means you know what you're doing. Usually, you do not need
those columns, but if you need them, go to solution3!

Solution2:

Turning off only_full_group_by. MYSQL will show you one of the two possible results RANDOMLY!! (It's ok if you do not really care what id it will choose)

Solution3

Use an Aggregate function like MIN(), MAX() to help MYSQL to decide what it must choose.

For example, I want the minimum id:

SELECT MIN(id), name FROM users GROUP BY name;

result:

  1      ali
  2      john     

It will choose the ali row which has the minimum id.

https://www.w3schools.com/sql/sql_having.asp 

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

https://www.w3schools.com/sql/sql_orderby.asp

ORDER BY Several Columns Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;