How to select only one data from duplicate data in mysql?

Question

I have table bio

ID   Name    Country   Address
1    Dan     America    A
2    Dan     Japan      B
3    Dan     Canada     C
4    Marcus  China      D
5    Kurtis  Nepal      E
6    Kurtis  Mexico     F
7    Jack    Indonesia  G

I need to select only one from the duplicate value of column "Name". I expect the result like this.

ID   Name    Country   Address
1    Dan     America    A
4    Marcus  China      D
5    Kurtis  Nepal      E
7    Jack    Indonesia  G

I used this query

SET SESSION sql_mode = ( SELECT REPLACE ( @@sql_mode, 'ONLY_FULL_GROUP_BY', '' ) );
Select * from bio group by name;

Is there any other way without using SET SESSION sql_mode = ( SELECT REPLACE ( @@sql_mode, 'ONLY_FULL_GROUP_BY', '' ) ); since if i didn't use that, it return error.

I have tried answer with forpass answer but it run very slow. Here is the Explain query.

id  select_type         table   type    possible_keys   rows    filtered Extra
1   PRIMARY             b       ALL                     1095012  100.00  Using where
2   DEPENDENT SUBQUERY  t       ALL     PRIMARY,semua   1095012  3.33    Range checked for each record (index map: 0x3)

Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results). — Tim Biegeleisen, Dec 11 '20 at 07:54
Please check the above link for some options. This question gets asked several times per day on this site. — Tim Biegeleisen, Dec 11 '20 at 07:54
*I need to select only one from the duplicate value of column "Name"* which one? What is the logig behind the expected result? — Jens, Dec 11 '20 at 07:55
in my table i have 3 duplicate value "Dan" in column name. I need to select only one data from the 3 duplicate value. same with Value "Kurtis" there are two duplicate value. I need to take only one data from 2 duplicate data of "Kurtis". @Jens — Gagantous, Dec 11 '20 at 08:00

Arun Palanisamy · Answer 1 · 2020-12-11T08:35:10.473

1

It can be easily achieved in MySQL 8.0 using the ROW_NUMBER() OVER (PARTITION BY ) window function. But in 5.7 you have to emulate the same function with variables. Something like below.

SELECT ID, Name, Country, Address
FROM (
    SELECT *, IF (@prev <> name, @rn: = 0, @rn),
        @prev: = Name,
        @rn: = @rn + 1 AS rn
    FROM bio, 
          (SELECT @rn: = 0 ) rn, 
          (SELECT @prev: = '') prev
    ORDER BY Address ASC
    ) t
WHERE rn = 1;

Alternatively you can use simple join to avoid mentioning the column names

SELECT b1.*
FROM bio b1
JOIN
(
    SELECT Name, Min(ID) AS ID FROM bio
    GROUP BY Name
) b2
ON b1.Name = b2.Name AND b1.ID = b2.ID;

edited Dec 11 '20 at 08:35

answered Dec 11 '20 at 08:15

Arun Palanisamy

5,281
6
28
53

is there a way that i dont need to select each column ? so i just need to `*`. Because i have many column. – Gagantous Dec 11 '20 at 08:27
It is not possible with variables. You can use the `join` way to avoid that. I have updated my answer @Gagantous – Arun Palanisamy Dec 11 '20 at 08:38

score 1 · Accepted Answer · answered Dec 11 '20 at 08:26

1

You can do it with NOT EXISTS:

SELECT b.*
FROM bio b
WHERE NOT EXISTS (
  SELECT 1
  FROM bio t
  WHERE t.Name = b.Name AND t.ID < b.ID
)

answered Dec 11 '20 at 08:26

forpas

160,666
10
38
76

i have 1 million data, it run really slow to return the result even i have indexed the table. – Gagantous Dec 11 '20 at 08:30
@Gagantous in MySql 5.7, without window functions, there aren't many options for this requiremnet. Either you use NOT EXISTS or aggregation and a join (which normally is less efficient than NOT EXISTS), or a simulation of row_number like Arun's answer. – forpas Dec 11 '20 at 08:38

How to select only one data from duplicate data in mysql?

2 Answers2