mysql using DISTINCT for only one column

Question

Is there a way to use DISTINCT (or another keyword) to not display duplicate results on one column? For example if I have a table with columns: id, name and countryCode

id name countryCode
1  Dan  IE
2  John US
3  John UK
4  Bob  DE

And I don't want to display duplicates where the name is the same so the result would be:

id name countryCode
1  Dan  IE
2  John US
4  Bob  DE

If I use DISTINCT here it needs to match the whole row but I only want to omit a row if the names match. Is there a way to do this? I found a similar solution here:DISTINCT for only one Column

But this does not work for mySQL. Any help would be much appreciated.

distinct is not a function so you can't use it against only one column, it works on all columns selected. in your example why did you choose row 2 for John and not row 3? — Niagaradad, Feb 02 '16 at 17:39
I don't mind which countryCode to take but if there is a way to select one or the other, that would be be nice to know — daninoz03, Feb 03 '16 at 09:49

score 6 · Accepted Answer · answered Feb 02 '16 at 17:45

For MySQL only (and related distributions, like MariaDB), you can use GROUP BY:

SELECT id, name, countryCode FROM tablename GROUP BY name

Please note that the row to be omitted is not influenced by ORDER BY or similar statements.

This is a MySQL behavior only, traditional handling of GROUP BY states that it should only be used together with aggregated values (see MySQL Handling of GROUP BY).

Giorgos Betsos · Answer 2 · 2017-03-19T19:13:51.077

0

In MySQL you can use variables in order to simulate ROW_NUMBER window function:

SELECT id, name, countrycode
FROM (
  SELECT id, name, countrycode,
         @rn := IF (@name = name, @rn + 1,
                    IF (@name := name, 1, 1)) AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @name := '') AS vars
  ORDER BY name, countrycode) AS t
WHERE t.rn = 1

The above query, in case of a name duplicate, selects the row having the first countrycode in alphabetical order.

Demo here

edited Mar 19 '17 at 19:13

answered Feb 02 '16 at 17:46

Giorgos Betsos

71,379
9
63
98

Thanks Giorgos Betsos. I tried to get this method working but it didn't like some of my column joins from other tables. I'm no pro when it comes to SQL so I just went for the GROUP BY approached mentioned above and it worked. Thanks anyway – daninoz03 Feb 03 '16 at 17:34

score 0 · Answer 3 · answered Feb 02 '16 at 17:47

0

Try this:

SELECT id,name, countryCode FROM table GROUP BY countryCode

answered Feb 02 '16 at 17:47

JesusIniesta

10,412
1
35
28

Grouping should be by name, not countryCode. – Jsowa May 07 '20 at 04:33

Ullas · Answer 4 · 2016-02-02T18:12:52.380

0

Give a row number for each group.

Query

select t1.id, t1.name, t1.countryCode from 
(
    select id, name, countryCode, 
    (
        case name when @curA 
        then @curRow := @curRow + 1 
        else @curRow := 1 and @curA := name end 
    ) + 1 as rn 
    from tblNames t, 
    (select @curRow := 1, @curA := '') r 
    order by id 
)t1 
where t1.rn = 1
order by t1.id;

SQL Fiddle demo

edited Feb 02 '16 at 18:12

answered Feb 02 '16 at 18:06

Ullas

11,450
4
33
50

Thanks Ullas. I tried to get this method working but it didn't like some of my column joins from other tables. I'm no pro when it comes to SQL so I just went for the GROUP BY approached mentioned above and it worked. Thanks anyway – daninoz03 Feb 03 '16 at 17:32

mysql using DISTINCT for only one column

4 Answers4

Linked