CODEWITHSUNDEEP
mysql
Johan
Johan

Reputation: 21

show if a value is unique in mysql query

I have a MySQL query. In this query I want to make a column where I can show if the value in the column Phone is unique or not. How to do this?

This is the query:

SELECT ID, Phone
FROM tbltest

Upvotes: 1

Views: 67

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

We can use COUNT() here as an analytic function:

SELECT ID, Phone,
       CASE WHEN COUNT(*) OVER (PARTITION BY Phone) = 1 THEN 'unique' END AS label
FROM tbltest
ORDER BY ID;

Here is a version which should work on earlier versions of MySQL:

SELECT t1.ID, t1.Phone,
       CASE WHEN t2.cnt = 1 THEN 'unique' END AS label
FROM tbltest t1
INNER JOIN
(
    SELECT Phone, COUNT(*) AS cnt
    FROM tbltest
    GROUP BY Phone
) t2
    ON t2.Phone = t1.Phone;

Upvotes: 3

Related Questions