57

In my table I have several duplicates. Ineed to find unique values in mysql table column.

SQL

SELECT column FROM table
WHERE column is unique

SELECT column FROM table
WHERE column = DISTINCT

I've been trying to Google, but almost all queries are more complex.

The result I's like is all non duplicate values.

EDIT I'd like to have UNIQUE values...

Not all values one time... (Distinct)

dda
  • 6,030
  • 2
  • 25
  • 34
Björn C
  • 3,860
  • 10
  • 46
  • 85

4 Answers4

95

Try to use DISTINCT like this:

SELECT DISTINCT mycolumn FROM mytable

EDIT:

Try

select mycolumn, count(mycolumn) c from mytable
group by mycolumn having c = 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 4
    I cannot use this. Because it will give me all values from column. I´d like to filter out the values that is duplicates. So that i select values that only appears once! – Björn C Dec 16 '15 at 13:07
  • 4
    @Mjukis:- Updated my answer. Also your question has no explanation about what you said in comments! :( – Rahul Tripathi Dec 16 '15 at 13:11
  • @Mjukis:- saying unique values does not make sense. You need to explicitly state that you are looking for `non duplicate values` excluding `duplicates` – Rahul Tripathi Dec 16 '15 at 13:13
  • 1
    ok Master... the updated code works fine. Thank you very much! – Björn C Dec 16 '15 at 13:15
  • I don't get it "group by mycolumn having c = 1" – Zbyszek Feb 16 '18 at 10:29
  • @Zbyszek `c` is aliased to `count(mycolumn)`, so it's basically saying to only return groups with a count of 1 if I'm not mistaken. – Doktor J Feb 21 '18 at 19:39
  • FYI, you can use `DISTINCT` for a specific column by wrapping it in parentheses. So, if you want to make sure `mycolumn` is unique but also bring back `anothercolumn` that you don't care about being unique, just do `SELECT DISTINCT( mycolumn ), anothercolumn FROM mytable`. – Joshua Pinter Mar 18 '18 at 17:00
13

Here is the query that you want! 

SELECT column FROM table GROUP BY column HAVING COUNT(column) = 1

This query took 00.34 seconds on a data set of 1 Million rows.

Here is a query for you though, in the future if you DO want duplicates, but NOT non-duplicates...

SELECT column, COUNT(column) FROM table GROUP BY column HAVING COUNT(column) > 1

This query took 00.59 seconds on a data set of 1 Million rows. This query will give you the (column) value for every duplicate, and also the COUNT(column) result for how many duplicates. You can obviously choose not to select COUNT(column) if you don't care how many there are.

You can also check this out, if you need access to more than just the column with possible duplicates... Finding duplicate values in a SQL table

Nerdi.org
  • 895
  • 6
  • 13
  • *if you DO want duplicates, but NOT non-duplicates...* what does that mean? – TheRealChx101 Nov 05 '21 at 13:20
  • 2
    @TheRealChx101 So, OP wants to see all values where there is NOT a duplicate of it anywhere. I showed that in query 1. In my 2nd query I show how you could show every value that DOES have a duplicate, and hide ones that do not. So I showed how to do what they want, and the opposite of what they want too. – Nerdi.org Nov 05 '21 at 17:40
1

Try this:

SELECT DISTINCT (column_name) FROM table_name
Lee Goddard
  • 10,680
  • 4
  • 46
  • 63
Bernes Lase
  • 67
  • 9
1

Try this one:

SELECT COUNT(column_name) AS `counter`, column_name 
FROM tablename 
GROUP BY column_name 
WHERE COUNT(column_name) = 1

Have a look at this fiddle: http://sqlfiddle.com/#!9/15147/2/0

Marcus
  • 1,910
  • 2
  • 16
  • 27