CODEWITHSUNDEEP
sqlsql-servert-sql
JackyBoi
JackyBoi

Reputation: 2773

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?

Upvotes: 63

Views: 116900

Answers (11)

Dark Storm
Dark Storm

Reputation: 329

for others who looks for the primary key you can execute the following command (sql server) :

exec sp_columns your_table

and take a look a the column TYPE_NAME you will see a type of int identity , that is the primary key in your table.

enter image description here

Upvotes: 0

Stijn
Stijn

Reputation: 311

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 which is less clear when using a HAVING clause.

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

Upvotes: 13

Schummi
Schummi

Reputation: 1

this code return distinct value

SELECT code FROM #test
group by code
having count(distinct code)= count(code)

return 14 that is just unique value

enter image description here

Upvotes: -1

Fernando Gomes
Fernando Gomes

Reputation: 1

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;

Upvotes: 0

Govind Gupta
Govind Gupta

Reputation: 1705

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

Upvotes: 0

Dgan
Dgan

Reputation: 10295

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'.

Upvotes: 71

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 2

TT.
TT.

Reputation: 16143

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.

Upvotes: 18

Ricardo Sanchez
Ricardo Sanchez

Reputation: 6289

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.

Upvotes: -1

FuzzyTree
FuzzyTree

Reputation: 32402

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.

Upvotes: 49

Matt Jones
Matt Jones

Reputation: 509

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;

Upvotes: 1

Related Questions