MySQL Select group by


The GROUP BY keyword in MySQL is used to group groups of one or more fields and returns the first record of a duplicate record.

grammar:

SELECT column, ... FROM tb_name GROUP BY column1, column2 ...

User table records are as follows:

uid username
1John
2Jack
3Marry
4John
5Marry

The user table above inquiries are as follows:

SELECT * FROM user GROUP BY username

The results are as follows:

uid username
1John
2Jack
3Marry

GROUP BY syntax in the MySQL database usage and other databases vary widely. For standard SQL, GROUP BY must be used in conjunction with aggregate functions, and the selected fields must appear in GROUP BY in addition to the aggregate functions. But in MySQL extended GROUP BY function:

  • Without the aggregate function, the result returned is the first row in the GROUP BY result set, as shown in the example above.
  • GROUP BY When combined with aggregate functions, the selected field does not have to exist in the GROUP BY, MySQL has the implied field function.

So we can MySQL GROUP BY on the expansion of features, combined with other keywords such as ORDER BY, etc., to facilitate the query results you want.


Example 2:

SELECT * FROM user GROUP BY username, uid

The results are as follows:

uid username
1John
4John
2Jack
3Marry
5Marry



Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook