'SQL query to determine that values in a column are unique

How to write a query to just determine that the values in a column are unique?

sqlsql-servertsql


Solution 1:[1]

Try this:

SELECT CASE WHEN count(distinct col1)= count(col1)
THEN 'column values are unique' ELSE 'column values are NOT unique' END
FROM tbl_name;

Note: This only works if 'col1' does not have the data type 'ntext' or 'text'. If you have one of these data types, use 'distinct CAST(col1 AS nvarchar(4000))' (or similar) instead of 'distinct col1'.

Solution 2:[2]

select count(distinct column_name), count(column_name)
from table_name;

If the # of unique values is equal to the total # of values, then all values are unique.

Solution 3:[3]

IF NOT EXISTS ( 
  SELECT
    column_name
  FROM
    your_table
  GROUP BY
    column_name
  HAVING
    COUNT(*)>1
)
  PRINT 'All are unique'
ELSE
  PRINT 'Some are not unique'

If you want to list those that aren't unique, just take the inner query and run it. HTH.

Solution 4:[4]

With this following query, you have the advantage of not only seeing if your columns are unique, but you can also see which combination is most non-unique. Furthermore, because you still see frequency 1 is your key is unique, you know your results are good, and not for example simply missing; something is less clear when using a HAVING clause. 

SELECT Col1, Col2, COUNT(*) AS Freq
FROM Table
GROUP BY Col1, Col2
ORDER BY Freq DESC

Solution 5:[5]

Are you trying to return only distinct values of a column? If so, you can use the DISTINCT keyword. The syntax is: 

SELECT DISTINCT column_name,column_name
FROM table_name;

Solution 6:[6]

If you want to check if all the values are unique and you care about NULL values, then do something like this:

select (case when count(distinct column_name) = count(column_name) and
                  (count(column_name) = count(*) or count(column_name) = count(*) - 1)
             then 'All Unique'
             else 'Duplicates'
        end)
from table t;

Solution 7:[7]

select (case when count(distinct column1 ) = count(column1)
             then 'Unique'
             else 'Duplicates'
        end)
from table_name

Solution 8:[8]

By my understanding you want to know which values are unique in a column. Therefore, using select distinct to do so doesn't solve the problem, because only lists the value as if they are unique, but they may not.

A simple solution as follows:

SELECT COUNT(column_name), column_name
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) = 1;

Solution 9:[9]

Use the DISTINCT keyword inside a COUNT aggregate function as shown below:

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

The above query will give you the count of distinct values in that column.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Michelle Turner
Solution 2 FuzzyTree
Solution 3 TT.
Solution 4 Stijn
Solution 5 Matt Jones
Solution 6 Gordon Linoff
Solution 7 Govind Gupta
Solution 8 Fernando Gomes
Solution 9 Ricardo Sanchez

Related Questions