Is unique?

[HenriIV]

hi,

I'm trying to check if a column has unique values in a field, how to do that?
(I tried IS UNIQUE condition but it doesn't work)

[lilleman]

Hi,

Try the following:

SELECT a.column
FROM table_name as a
  LEFT JOIN table_name as b
    ON b.column = a.column
HAVING COUNT(b.column) = 1

[stereofrog]

SELECT 
    COUNT(col) = COUNT(DISTINCT col) 
            AS col_is_unique
FROM table

[lilleman]

Hi,

SELECT 
    COUNT(col) = COUNT(DISTINCT col) 
            AS col_is_unique
FROM table

That's a very smart solution. However, it won't work unless you add a GROUP BY clause.

[HenriIV]

Damn, none of these does work, I must have something wrong somewhere

[lilleman]

Hi,

I tested sterefrog's solution (after I added a GROUP BY clause to it), and it worked like a charm. Are you sure that all field/table names are correct? This is the query I used:

SELECT str, COUNT(str) =
  COUNT(DISTINCT str) as is_unique
FROM test_table GROUP BY str

[HenriIV]

Yes it worked when I added the GROUP BY statement ;o)
Thanks to you guys

[stereofrog]

However, it won't work unless you add a GROUP BY clause.

Why?

[lilleman]

Hi,

Why?

Since no GROUP BY clause exists, your query will check if the total number of rows in the table is the same as the number of unique values in the column. So basically, your query check if all values are unique, not if a specific value is unique.

[stereofrog]

Yes, and it's what OP has asked for.

I'm trying to check if a column has unique values in a field, how to do that?

If you need to count some specific value, just use

SELECT COUNT(*) FROM .... WHERE col=....

If result isn't 1, the value is not 'unique'.

SELECT str, COUNT(str) =
COUNT(DISTINCT str) as is_unique
FROM test_table GROUP BY str

COUNT(DISTINCT x) together with "GROUP BY x" always gives 1, so it's no need to calculate it. This returns all values that occur more than once:

SELECT col, COUNT(*) AS c, 
   FROM tbl
   GROUP by col
   HAVING c > 1

[lilleman]

Hi,

Yes, and it's what OP has asked for.

I was under the impression that he wanted to check whether or not a specific value is unique (which is what your query does if a GROUP BY clause is specified).

[stereofrog]

Ok, the main thing is that it works for him now, whatever he wanted

[HenriIV]

the main thing is that it works for him now

Yes, it' ok now ;o)

The fact is that it always return 1 without GROUP BY, with GROUP BY it works perfectly