Basic use tutorials for distinct and group by statements in Mysql _mysql

Source: Internet
Author: User
Tags first row

MySQL Distinct Delete Query results duplicate records
DISTINCT
Use the DISTINCT keyword to remove duplicate records for a field in a query.
Grammar:

SELECT DISTINCT (column) from Tb_name


Example:
Assume that the user table has the following record:

UID username
1 Xiao Li
2 xiao Zhang
3 xiao Li
4 xiao Wang
5 xiao Li
6 Xiao Zhang

SQL statement:
SELECT DISTINCT (username) from user
Return query results as follows:

Username Xiao
Li
Xiao Zhang
Xiao Wang

Tips
Using the DISTINCT keyword to remove duplicate records has great limitations. DISTINCT () can only contain one field and the query result returns only that field, not the complete record of the data (as shown in the previous example).
You can try using the following syntax:

SELECT DISTINCT (column), Column1,column2,... From Tb_name

The query results will return all the fields listed, but the query often invalidates column uniqueness and column 1,column 2,... cannot be placed before DISTINCT (column).
The example above should return the following result (which is often expected):

UID username
1 Xiao Li
2 xiao Zhang
3 Xiao Wang

The GROUP by keyword will be used at this time.

MySQL GROUP BY Data grouping
GROUP by
MySQL uses the group by keyword to group queries for one or some of the fields, and returns the first of the duplicate records.
Grammar:

SELECT column,... From Tb_name GROUP by column1,column2 ...

The user table records are as follows:

UID username
1 Xiao Li
2 xiao Zhang
3 xiao Li
4 xiao Wang
5 xiao Li
6 Xiao Zhang

Check the user table above for the following:

SELECT * from user GROUP by username

Return query results as follows:

UID username
1 Xiao Li
2 xiao Zhang
3 Xiao Wang

Description
The use of the GROUP by syntax in the MySQL database differs greatly from that of other databases. For standard SQL, group by must be used in conjunction with aggregate functions, and the selected field must appear in GROUP by, in addition to aggregate functions. However, the functionality of GROUP by IS extended in MySQL:
Without the aggregate function, the result returned is the first row in the GROUP by result set, as shown in the previous example.
When group by combines aggregate functions, the selected fields do not have to exist in GROUP by, and MySQL has the ability to suppress fields.
So we can according to MySQL on the GROUP by the extended characteristics, combined with some other keywords such as order by and so on, convenient to get the desired results of the query.
Example 2:

SELECT * from user GROUP by Username,uid

Return query results as follows:

UID username
1 Xiao Li
3 xiao Li
5 xiao Li
2 xiao Zhang
6 xiao Zhang
4 Xiao Wang

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.