SQL query to determine if values ​​in a column are unique

Question

How to write a query to simply determine that the column values ​​are unique?

Answer 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 the column "col1" does not have a data type of "ntext" or "text". If you have one of these data types, use "separate CAST (col1 AS nvarchar (4000))" (or similar) instead of "excellent col1".

Answer 2

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

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

Answer 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 are not unique, just take an internal query and run it. NTN.

Answer 4

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

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

Answer 5

If you want to check if all 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; 

Answer 6

Are you trying to return only individual column values? If so, you can use the DISTINCT keyword. Syntax:

 SELECT DISTINCT column_name,column_name FROM table_name; 

Answer 7

Use the DISTINCT keyword inside the COUNT aggregation function, as shown below:

 SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name 

The above query will give you the number of different values ​​in this column.