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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x