CODEWITHSUNDEEP
mysqlgroup-by
Gagantous
Gagantous

Reputation: 518

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

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)

Upvotes: 0

Views: 1459

Answers (2)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

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;

Upvotes: 1

forpas
forpas

Reputation: 164064

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
)

Upvotes: 1

Related Questions