Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: SELECT UNIQUE VALUE

Tags:

mysql

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)

like image 929
Björn C Avatar asked Dec 16 '15 12:12

Björn C


People also ask

How do you SELECT unique records from the particular column?

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

How do you get unique values from a database?

The unique values are fetched when we use the distinct keyword. SELECT DISTINCT returns only distinct (different) values. DISTINCT eliminates duplicate records from the table. DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.

Can we use distinct in MySQL?

We can use the MySQL DISTINCT clause to return a single field that removes the duplicates from the result set. For example: SELECT DISTINCT state FROM customers; This MySQL DISTINCT example would return all unique state values from the customers table.

What does distinct do in MySQL?

MySQL DISTINCT clause is used to remove duplicate records from the table and fetch only the unique records. The DISTINCT clause is only used with the SELECT statement.


Video Answer


2 Answers

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 
like image 153
Rahul Tripathi Avatar answered Oct 05 '22 15:10

Rahul Tripathi


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

like image 27
Nerdi.org Avatar answered Oct 05 '22 14:10

Nerdi.org