How to use SELECT distinct in MySQL

Source: Internet
Author: User

When using MySQL, it is sometimes necessary to query for a record that does not duplicate a field, although MySQL provides the DISTINCT keyword to filter out redundant duplicate records to keep only one, but it is often used only to return the number of distinct records, instead of using it to return all values that are not re-recorded. The reason is that distinct can only return its target field, but cannot return other fields, after experiment, it is possible to implement the following methods.

Examples are as follows:
This is the structure of the test table.
ID test1 test2
1 a 1
2 a 2
3 a 3
4 a 1
5 B 1
6 B 2
7 B 3
8 B 2
For example, if I want to use a single statement to query for all the data that test1 does not repeat, then you must use distinct to remove the redundant duplicate records.
Select distinct test1 from test
The resulting results are:
Test1
A
B
Seems to be working, but what I want to get is the ID value? Change the query statement:
SELECT DISTINCT test1, ID from test

Test1 ID
A 1
A 2
A 3
A 4
B 5
B 6
B 7
B 8
Why doesn't distinct work? The role is up, but he also functions two fields, that is, must have the same ID and test1 will be excluded, this is not possible, the ID is automatically increased ...

Let's change the query statement:

Select ID, distinct test1 from test
Unfortunately, in addition to the error message you get nothing, distinct must be placed at the beginning. Is it difficult to put distinct in a where condition? Can, still error ....

The manual can be accessed through Group_cancat:
SELECT ID, Group_concat (DISTINCT test1) from test group by Test1
ID Group_concat (DISTINCT test1)
2 B
5 b
However, it can only be used after 4.1.0, for those older versions of the database is not.

Other functions can be implemented:
SELECT *, COUNT (distinct test1) from test group by Test1
ID test1 test2 count (distinct test1)
1 a 1 1
5 B 1 1
The last item is superfluous, do not care on the line, the purpose to achieve ...

There are also simpler ways to do this:
Select ID, test1 from test group by Test1
ID test1
2 B
5 b

How to use SELECT distinct in MySQL

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.